Oracle分区表drop和truncate partition索引失效问题

        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分区又会有什么不同呢，或者有其他需要注意的地方没。