2014年（88）
分类： Oracle
2014-06-17 14:31:10
续接上次的分区表学习笔记，对分区索引进行了总结。
--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
