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

來源:互聯網
上載者:User

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

相關文章

聯繫我們

該頁面正文內容均來源於網絡整理,並不代表阿里雲官方的觀點,該頁面所提到的產品和服務也與阿里云無關,如果該頁面內容對您造成了困擾,歡迎寫郵件給我們,收到郵件我們將在5個工作日內處理。

如果您發現本社區中有涉嫌抄襲的內容,歡迎發送郵件至: info-contact@alibabacloud.com 進行舉報並提供相關證據,工作人員會在 5 個工作天內聯絡您,一經查實,本站將立刻刪除涉嫌侵權內容。

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.