Oracle 分区表,分区索引

一：建立一个分区表，并插入数据：

SQL> create table part_tab (id1 number,id2 number)
2 partition by RANGE (id1)
3 (
4 partition p1 values less than (20),
5 partition p2 values less than (40),
6 partition p3 values less than (60),
7 partition p4 values less than (maxvalue)
8 );

Table created.

SQL>


SQL> insert into part_tab select user_id,trunc(dbms_random.value(1,100)) from dba_users;

37 rows created.

SQL>
SQL> commit;

Commit complete.

二：建立索引

   在分区表上建立索引可以分为全局索引和本地索引：全局索引是针对整张表建立的一个索引，可以在建立索引的时候根据索引键进行相应的分区；

 

SQL> create index global_index on part_tab(id1) global
2 partition by range(id1)
3 (
4 partition p1 values less than (50),
5 partition p2 values less than (maxvalue)
6 );

Index created.

SQL> create index global_index2 on part_tab(id2) global
2 partition by range(id2)
3 (
4 partition p1 values less than (50),
5 partition p2 values less than (maxvalue)
6 );

Index created.

以上就是对id1和id2字段建立了2个全局索引

 

建立全局索引的时候索引键必须是索引分区键的前缀，不然建立索引的时候会报错：

 

SQL> create index global_index2 on part_tab(id2) global
2 partition by range(id1)
3 (
4 partition p1 values less than (50),
5 partition p2 values less than (maxvalue)
6 )
7 ;
partition by range(id1)
*
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed

SQL> create index global_index5 on part_tab(id2,id1) global
2 partition by range(id2)
3 (
4 partition p1 values less than (50),
5 partition p2 values less than (maxvalue)
6 );

Index created.

如果建立本地索引的时候，要建立的字段已经建立的全局索引，那建立这个索引的时候也要报错，反之亦然：

SQL> create index local_index1 on part_tab(id1) local;

create index local_index1 on part_tab(id1) local

*

ERROR at line 1:

ORA-01408: such column list already indexed



SQL> create index local_index1 on part_tab(id2) local;

create index local_index1 on part_tab(id2) local

*

ERROR at line 1:

ORA-01408: such column list already indexed

 删除其中之一就可以正确建立：

SQL> drop index global_index2;

Index dropped.

SQL> create index local_index1 on part_tab(id2) local;

Index created.

三：增加分区

    增加分区的时候，本地索引会自动更新，会自动增加相应的分区索引；而全局索引则不会，如果增加分区的时候没加上update global indexes 字句的时候，全局索引就会失效：

 

在原先分区表的基础上增加一个分区

SQL> alter table part_tab add partition p5 values less than (80);
alter table part_tab add partition p5 values less than (80)
*
ERROR at line 1:
ORA-14074: partition bound must collate higher than that of the last partition

报错，意思就是说现在要建立的分区值必须大于最后一个的分区值

 

这里可以有2种方法：

 1：把大的那个分区删掉，再增加相应的分区，但这样会丢失数据

 

SQL> select * from part_tab where id1 > 60;

ID1 ID2
---------- ----------
86 7
88 18
70 25
65 34
78 51
76 57
67 59
85 60
91 62
89 63
74 66

ID1 ID2
---------- ----------
75 66
87 68
84 73
2147483638 81
83 82
72 89
79 89
61 92
90 95

20 rows selected.

SQL> alter table part_tab drop partition p4 update global indexes;

Table altered.

SQL> select * from part_tab where id1 > 60;

no rows selected

SQL> alter table part_tab add partition p4 values less than (maxvalue);

Table altered.

2：SPLIT PARTITION，分割那个分区，但对应分区上的本地索引会失效，分割完以后要重建这个本地索引

SQL> select count(*) from part_tab where id1 > 60;

COUNT(*)
----------
20

SQL> ALTER TABLE part_tab SPLIT PARTITION p4 AT (80) INTO (PARTITION P5, PARTITION P4) UPDATE GLOBAL INDEXES ;

Table altered.

SQL> select count(*) from part_tab where id1 > 60;

COUNT(*)
----------
20

SQL> select * from part_tab where id1 > 80;

ID1 ID2
---------- ----------
90 4
2147483638 5
89 21
84 24
86 28
83 36
87 52
85 62
88 70
91 95

10 rows selected.

SQL> select index_name,partition_name,status from user_ind_partitions;

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
GLOBAL_INDEX P1 USABLE
GLOBAL_INDEX P2 USABLE
LOCAL_INDEX1 P2 USABLE
LOCAL_INDEX1 P1 USABLE

INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
LOCAL_INDEX1 P3 USABLE
GLOBAL_INDEX5 P1 USABLE
GLOBAL_INDEX5 P2 USABLE
LOCAL_INDEX1 P5 UNUSABLE
LOCAL_INDEX1 P4 UNUSABLE


SQL> alter index local_index1 rebuild partition p4;

Index altered.

SQL> alter index local_index1 rebuild partition p5;

Index altered.