Oracle分區表drop和truncate partition索引失效問題____Oracle

       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分區又會有什麼不同呢，或者有其他需要注意的地方沒。