DBA的维护管理中,索引失效是一常见的问题,比如move、ctas、交换分区等都会造成索引的失效,以下实验是验证drop partition、truncate partition分区对本地索引和全局索引的影响,在平常中,truncate table表是不会对索引失效的。
基础环境
SQL> create table ou_part (a integer)
2 partition by range(a)
3 (
4 PARTITION OU_PART_01 VALUES less than(10) tablespace TS_OU_01,
5 partition ou_part_02 values less than(20) tablespace ts_ou_02,
6 partition ou_part_03 values less than(30) tablespace ts_ou_03,
7 partition ou_part_04 values less than(40) tablespace ts_ou_04
8 );
Table created
Executed in 0.016 seconds
SQL> insert into ou_part values (1);
1 row inserted
Executed in 0 seconds
SQL> insert into ou_part values (11);
1 row inserted
Executed in 0 seconds
SQL> insert into ou_part values (21);
1 row inserted
Executed in 0 seconds
SQL> insert into ou_part values (31);
1 row inserted
Executed in 0 seconds
SQL> commit;
Commit complete
实验1
SQL> create index index_glo on ou_part (a) global;
Index created
Executed in 0 seconds
SQL> select status ,index_name from user_indexes where index_name = 'INDEX_GLO';
STATUS INDEX_NAME
-------- ------------------------------
VALID INDEX_GLO
Executed in 0.015 seconds
SQL> alter table ou_part truncate partition ou_part_01;
Table truncated
Executed in 0 seconds
SQL> select * from ou_part;
A
---------------------------------------
11
21
31
Executed in 0.016 seconds
此时发现 a=1的数据已经被删除。
SQL> select status ,index_name from user_indexes where index_name = 'INDEX_GLO';
STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE INDEX_GLO
Executed in 0 seconds
此时索引变为了不可用状态,说明当truncate一个分区时,索引会失效。
SQL> alter index index_glo rebuild;
Index altered
Executed in 0 seconds
SQL> select status ,index_name from user_indexes where index_name = 'INDEX_GLO';
STATUS INDEX_NAME
-------- ------------------------------
VALID INDEX_GLO
Executed in 0 seconds
SQL> alter table ou_part drop partition ou_part_02;
Table altered
Executed in 0 seconds
SQL> select * from ou_part;
A
---------------------------------------
21
31
Executed in 0 seconds
SQL> select status ,index_name from user_indexes where index_name = 'INDEX_GLO';
STATUS INDEX_NAME
-------- ------------------------------
UNUSABLE INDEX_GLO
Executed in 0 seconds
此时索引为不可用,说明drop 分区后索引不可用
实验1说明全局索引在drop partition、truncate partition后索引都会失效,对于Global index,Oracle提供了一参数update global indexes,可避免truncate或drop partition时索引失效问题,另外一种方法是rebuild,这2种方法各有利弊,在生产上不同的环境方法也不一样。
实验二
SQL> create index index_loc on ou_part (a) local;
Index created
Executed in 0 seconds
SQL> select a.status from User_Ind_Partitions a where a.Index_Name = 'INDEX_LOC';
STATUS
--------
USABLE
USABLE
USABLE
USABLE
Executed in 0.016 seconds
SQL> select status from User_Indexes where Index_Name = 'INDEX_LOC';
STATUS
--------
N/A
Executed in 0.016 seconds
SQL> alter table ou_part truncate partition ou_part_01;
Table truncated
Executed in 0.031 seconds
SQL> select a.status from User_Ind_Partitions a where a.Index_Name = 'INDEX_LOC';
STATUS
--------
USABLE
USABLE
USABLE
USABLE
Executed in 0.016 seconds
此时会发现truncate partition后,局部索引并没有失效,说明当truncate partition时会维护局部索引
SQL> alter table ou_part drop partition ou_part_01;
Table altered
Executed in 0 seconds
SQL> select a.status from User_Ind_Partitions a where a.Index_Name = 'INDEX_LOC';
STATUS
--------
USABLE
USABLE
USABLE
Executed in 0.016 seconds
对于局部索引来说,当删除分区表的一个分区时,相对应的,该分区的索引就一同被删除了。
对于索引失效问题,Oracle提供了2种处理方法
1、update global indexes,此种方法主要针对的是全局索引
2、rebuild,支持全局索引和本地索引
这里主要讲的是drop、truncate,那add、split、merge分区又会有什么不同呢,或者有其他需要注意的地方没。