公众号：杨建荣的学习笔记 · 作者：笔记77天 · 发布：2014-05-19 09:00:11 · 原文链接

续接上次的分区表学习笔记，对分区索引进行了总结。

–index maintance
SQL> select index_name,table_name from user_indexes where table_name=’RANGE_PART’;

no rows selected

–create one global index
SQL> create index glb_range_part on range_part(a,b)
2 global partition by range(a)
3 ( partition part_01 values less than(1000),
4 partition part_02 values less than(maxvalue)
5 );

Index created.
SQL> create index ind_range_part on range_part(a,b) local;
create index ind_range_part on range_part(a,b) local
*
ERROR at line 1:
ORA-01408: such column list already indexed
–如果已经定义了index的列，则不能再创建其他的索引
–再次验证
SQL> create index ind_range_part on range_part(a,b);

Index created.

SQL> create index ind_range_part on range_part(a,b) local;
create index ind_range_part on range_part(a,b) local
*
ERROR at line 1:
ORA-00955: name is already used by an existing object

–提示是index名字重复了，似乎可以重新建一个其他名字的index
SQL> create index ind1_range_part on range_part(a,b);
create index ind1_range_part on range_part(a,b)
*
ERROR at line 1:
ORA-01408: such column list already indexed
–这次还是回到上一步,错误重现

–如果不加local，索引在status列会有不同
SQL> select index_name,table_name,status from user_indexes where table_name=’RANGE_PART’;

INDEX_NAME TABLE_NAME STATUS
—————————— —————————— ——–
IND_RANGE_PART RANGE_PART VALID

SQL> drop index ind_range_part;

Index dropped.

SQL> create index ind_range_part on range_part(a,b) local;

Index created.

SQL> select index_name,table_name,status from user_indexes where table_name=’RANGE_PART’;

INDEX_NAME TABLE_NAME STATUS
—————————— —————————— ——–
IND_RANGE_PART RANGE_PART N/A

关于global index还有一点是global partition Index必须是prefixed的
SQL> create index ind_range_part on range_part(b,a)
global partition by range(a)
( partition part_01 values less than(1000),
partition part_02 values less than(maxvalue)
)
SQL> /
global partition by range(a)
*
ERROR at line 2:
ORA-14038: GLOBAL partitioned index must be prefixed

–清除所有的索引，重新测试

SQL> create index ind_range_part on range_part(a,b)
global partition by range(a)
( partition glb_part_01 values less than(1000),
partition glb_part_02 values less than(maxvalue)
)
2 3 4 5 6
SQL> /

Index created.

SQL> create index ind_range_part2 on range_part(b,a) local;

Index created.

1* select index_name,status from user_indexes where table_name=’RANGE_PART’
SQL> /

INDEX_NAME STATUS
—————————— ——–
IND_RANGE_PART N/A
IND_RANGE_PART2 N/A

SQL> select index_name,partition_name,high_value,status from user_ind_partitions where index_name in(select index_name from user_indexes where table_name=’RANGE_PART’);

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
—————————— —————————— ——————– ——–
IND_RANGE_PART GLB_PART_01 1000 USABLE
IND_RANGE_PART2 PART_05 MAXVALUE USABLE
IND_RANGE_PART2 PART_02 4000 USABLE
IND_RANGE_PART2 PART_01 2000 USABLE
IND_RANGE_PART GLB_PART_02 MAXVALUE USABLE

SQL> alter table range_part merge partitions part_01,part_02;

Table altered.

SQL> select index_name,partition_name,high_value,status from user_ind_partitions where index_name in(select index_name from user_indexes where table_name=’RANGE_PART’);

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
—————————— —————————— ——————– ——–
IND_RANGE_PART GLB_PART_01 1000 UNUSABLE
IND_RANGE_PART2 PART_05 MAXVALUE USABLE
IND_RANGE_PART GLB_PART_02 MAXVALUE UNUSABLE
IND_RANGE_PART2 SYS_P51 4000 UNUSABLE

–分区编译
SQL> alter index ind_range_part2 rebuild partition SYS_P51;

Index altered.
INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
—————————— —————————— ——————– ——–
IND_RANGE_PART GLB_PART_01 1000 UNUSABLE
IND_RANGE_PART2 PART_05 MAXVALUE USABLE
IND_RANGE_PART GLB_PART_02 MAXVALUE UNUSABLE
IND_RANGE_PART2 SYS_P51 4000 USABLE

–对全局索引全表rebuild失败
SQL> ALTER INDEX IND_RANGE_PART REBUILD;
ALTER INDEX IND_RANGE_PART REBUILD
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole
–只能根据分区来相应rebuild

SQL> ALTER INDEX IND_RANGE_PART REBUILD PARTITION GLB_PART_01;

Index altered.

SQL> ALTER INDEX IND_RANGE_PART REBUILD PARTITION GLB_PART_02;

Index altered.

SQL> ALTER TABLE RANGE_PART RENAME PARTITION SYS_P51 TO PART_01;

Table altered.

SQL> ALTER TABLE RANGE_PART SPLIT PARTITION PART_01 AT(2000) INTO (PARTITION PART_01,PARTITION PART_02);

Table altered.

SQL> select index_name,partition_name,high_value,status from user_ind_partitions where index_name in(select index_name from user_indexes where table_name=’RANGE_PART’);

INDEX_NAME PARTITION_NAME HIGH_VALUE STATUS
—————————— —————————— ——————– ——–
IND_RANGE_PART GLB_PART_01 1000 UNUSABLE
IND_RANGE_PART2 PART_05 MAXVALUE USABLE
IND_RANGE_PART2 PART_02 4000 UNUSABLE
IND_RANGE_PART2 SYS_P51 2000 UNUSABLE
IND_RANGE_PART GLB_PART_02 MAXVALUE UNUSABLE

–PARTITION_NAME为SYS_P51，这个是Index的partition_name
SQL> SELECT PARTITION_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME=’RANGE_PART’;

PARTITION_NAME
——————————
PART_01
PART_02
PART_05

–分区名字没有问题

–rebuild索引有以下的方式
SQL> ALTER TABLE RANGE_PART MODIFY PARTITION PART_01 REBUILD UNUSABLE LOCAL INDEXES;

Table altered.

SQL> ALTER INDEX IND_RANGE_PART2 REBUILD PARTITION PART_02;

Index altered.

最后有几个视图需要注意一下。

SQL> SELECT PARTITION_COUNT,STATUS,TABLE_NAME FROM USER_PART_TABLES WHERE TABLE_NAME=’RANGE_PART’;

PARTITION_COUNT STATUS TABLE_NAME
————— ——– ——————————
3 VALID RANGE_PART

–查找partition key
SQL> SELECT NAME,OBJECT_TYPE,COLUMN_NAME FROM USER_PART_KEY_COLUMNS WHERE NAME=’RANGE_PART’;

NAME OBJEC COLUMN_NAME
—————————— —– ——————–
RANGE_PART TABLE A

–USER_TAB_PARTITIONS
–USER_IND_PARTITIONS