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