ddl 導致分割槽表全域性索引unusable

上週五意外發現一個分割槽表的全域性索引失效變為unusable，在監控SQL效能時有幾個SQL因為這個索引而掛掉。
忽然想起是前一天進行測試時，對該表一個分割槽做了exchange交換分割槽操作。下面還原下場景：
create table test(id number,name varchar2(10))
partition by list(id)
(
 partition p1 values(1),
 partition p2 values(2),
 partition p3 values(3)
);
create index idx_test_id on test(id);--全域性索引
create index idx_test_name on test(name) local;--本地索引
SQL> select table_name,index_name,status from user_indexes where table_name='TEST';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
TEST                           IDX_TEST_NAME                  N/A
TEST                           IDX_TEST_ID                    VALID
N/A表示是本地索引狀態，VALID是全域性索引狀態。

1.交換分割槽（此時表中無資料）
SQL> create table t as select * from test;
表已建立。
SQL> alter table test exchange partition p1 with table t;
表已更改。
SQL> select table_name,index_name,status from user_indexes where table_name='TEST';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
TEST                           IDX_TEST_NAME                  N/A
TEST                           IDX_TEST_ID                    UNUSABLE
可以看到此時全域性索引由valid變為unusable，本地索引狀態沒有變。

2.truncate分割槽
首先將全域性索引重建：
alter index idx_test_id rebuild;
此時檢視
SQL> select table_name,index_name,status from user_indexes where table_name='TEST';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
TEST                           IDX_TEST_NAME                  N/A
TEST                           IDX_TEST_ID                    VALID
直接truncate分割槽看看，
SQL> alter table test truncate partition p1;
表被截斷。
SQL> select table_name,index_name,status from user_indexes where table_name='TEST';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
TEST                           IDX_TEST_NAME                  N/A
TEST                           IDX_TEST_ID                    VALID
直接truncate沒有資料的分割槽p1，全域性分割槽和本地分割槽索引狀態都不變。
SQL> insert into test values(1,'a');
已建立 1 行。
SQL> alter table test truncate partition p1;
表被截斷。
SQL> select table_name,index_name,status from user_indexes where table_name='TEST';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
TEST                           IDX_TEST_NAME                  N/A
TEST                           IDX_TEST_ID                    UNUSABLE
在p1分割槽插入資料後，再執行truncate分割槽，這時發現本地分割槽狀態沒有變，但是全域性分割槽狀態由valid變為unusable。

3.drop分割槽
首先將全域性索引重建：alter index idx_test_id rebuild;
SQL> select table_name,index_name,status from user_indexes where table_name='TEST';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
TEST                           IDX_TEST_NAME                  N/A
TEST                           IDX_TEST_ID                    VALID
SQL> alter table test drop partition p1;
表已更改。
SQL> select table_name,index_name,status from user_indexes where table_name='TEST';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
TEST                           IDX_TEST_NAME                  N/A
TEST                           IDX_TEST_ID                    VALID
分割槽中沒有資料時，執行drop分割槽不會影響全域性索引狀態。
SQL> insert into test values(2,'a');
已建立 1 行。
SQL> alter table test drop partition p2;
表已更改。
SQL> select table_name,index_name,status from user_indexes where table_name='TEST';
TABLE_NAME                     INDEX_NAME                     STATUS
------------------------------ ------------------------------ --------
TEST                           IDX_TEST_NAME                  N/A
TEST                           IDX_TEST_ID                    UNUSABLE

分割槽中有資料時執行drop分割槽，會改變全域性索引狀態。
而我們知道，全域性索引和本地索引執行dml都不會改變索引狀態；
但是從上面得知，在進行交換分割槽時會改變全域性索引狀態；當drop或truncate分割槽並且分割槽裡有資料時，會改變全域性索引狀態。
但是他們都不改變本地索引狀態。
有時你可能會忽然發現全域性索引失效，其實這對於生產庫來說是很可怕的事情。在操作時千萬要小心，如果不得不進行會引起全域性索引失效的操作，

那麼操作完畢一定要記得將全域性索引rebuild。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24496749/viewspace-734921/，如需轉載，請註明出處，否則將追究法律責任。