Partition table分區(qū)表刪除分區(qū)數(shù)據(jù)時導(dǎo)致索引失效怎么辦

小編給大家分享一下Partition table分區(qū)表刪除分區(qū)數(shù)據(jù)時導(dǎo)致索引失效怎么辦，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！

一、描述

       有個小任務(wù)就是要刪除些數(shù)據(jù)，哈哈，先自己小開心一下。因為要刪除的數(shù)據(jù)表是我之前轉(zhuǎn)換成的分區(qū)表。這個分區(qū)表是按照里面有個創(chuàng)建時間字段來分區(qū)的，1個季度為1個分區(qū)。所以我現(xiàn)在要將2017年7月1日之前的數(shù)據(jù)刪除（數(shù)據(jù)量約1000萬），可以直接刪除表分區(qū)數(shù)據(jù)就好。如果要是用delete去刪除這么多的數(shù)據(jù)，我還要寫存儲過程，分批提交的這樣做。就是這樣的一簡單的truncate partition 引發(fā)了后繼的業(yè)務(wù)故障。最終查詢到該表的索引失效，重建立后恢復(fù)。真是汗！

二、實驗

1.創(chuàng)建環(huán)境

1. SQL> create table TEST_PARTAS (id number(11), ACCOUNT_ID number(11) ,CTIME date)
   

2.   2  partition by range (CTIME)
   

3.   3  interval( NUMTOYMINTERVAL(3,'month'))
   

4.   4 (partition P0 values less than (TO_DATE('2016-01-01','yyyy-mm-dd')),
   

5.   5   partition p1 values less than (to_date('2017-01-01','yyyy-mm-dd')));
   

6. 
   

7. Table created.
   

8. 
   

9. SQL> insert into TEST_PARTAS select t.id,t.account_id,t.create_time from act_test t;
   

10. 3483178 rows created.
    

11. 
    

12. SQL> commit;
    

13. Commit complete.
    

14. 
    

15. SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS('SAM','TEST_PARTAS');
    

16. PL/SQL procedure successfully completed.

2.檢查分區(qū)表及數(shù)據(jù)

1. SQL> select count(*) from TEST_PARTAS;
   

2. 
   

3.   COUNT(*)
   

4. ----------
   

5.    3483178
   

6. 
   

7. SQL> set lines 120 pages 200;
   

8. SQL> set long 9999999
   

9. SQL> col table_name for a15
   

10. SQL> col PARTITION_NAME for a10
    

11. 
    

12. SQL> select t.table_name,t.partition_name,t.num_rows,t.blocks,t.interval,t.high_value from USER_TAB_PARTITIONS t;
    

13. 
    

14. TABLE_NAME      PARTITION_   NUM_ROWS     BLOCKS INT HIGH_VALUE
    

15. --------------- ---------- ---------- ---------- --- --------------------------------------------------
    

16. TEST_PARTAS     P0            2182116       6046 NO TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
    

17.                                                      MI:SS', 'NLS_CALENDAR=GREGORIAN')
    

18. 
    

19. TEST_PARTAS     P1             616290      36506 NO TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
    

20.                                                      MI:SS', 'NLS_CALENDAR=GREGORIAN')
    

21. 
    

22. TEST_PARTAS     SYS_P1611       44829       4030 YES TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:
    

23.                                                      MI:SS', 'NLS_CALENDAR=GREGORIAN')
    

24. 
    

25. TEST_PARTAS     SYS_P1612       21706       3022 YES TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:
    

26.                                                      MI:SS', 'NLS_CALENDAR=GREGORIAN')
    

27. 
    

28. TEST_PARTAS     SYS_P1613      172525       3022 YES TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:
    

29.                                                      MI:SS', 'NLS_CALENDAR=GREGORIAN')
    

30. 
    

31. TEST_PARTAS     SYS_P1614      442435       2014 YES TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:
    

32.                                                      MI:SS', 'NLS_CALENDAR=GREGORIAN')
    

33. 
    

34. TEST_PARTAS     SYS_P1615        3277        238 YES TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:
    

35.                                                      MI:SS', 'NLS_CALENDAR=GREGORIAN')
    

36. 
    

37. 
    

38. 7 rows selected.

3.創(chuàng)建主鍵和索引

1. SQL> alter table TEST_PARTAS add constraint pk_id primary key(ID);
   

2. Table altered.
   

3. 
   

4. SQL> CREATE INDEX IND_ACCOUNT_ID ON TEST_PARTAS (ACCOUNT_ID);
   

5. Index created.

4.檢查索引狀態(tài)，當(dāng)前狀態(tài)可用

1. SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
   

2. 
   

3. INDEX_NAME                     TABLE_NAME                     STATUS
   

4. ------------------------------ ------------------------------ --------
   

5. PK_ID                          TEST_PARTAS VALID
   

6. IND_ACCOUNT_ID                 TEST_PARTAS VALID

5.用truncate 刪除p0分區(qū)數(shù)據(jù)，不加update index參數(shù)

1. SQL> alter table test_partas truncate partition p0;
   

2. 
   

3. Table truncated.

6.檢查索引狀態(tài)，狀態(tài)不可用 

1. SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
   

2. 
   

3. INDEX_NAME                     TABLE_NAME                     STATUS
   

4. ------------------------------ ------------------------------ --------
   

5. PK_ID                          TEST_PARTAS UNUSABLE
   

6. IND_ACCOUNT_ID                 TEST_PARTAS UNUSABLE

7.重建立索引，要加online ，盡量減小對業(yè)務(wù)的沖擊

1. SQL> alter index PK_ID rebuild online;
   

2. 
   

3. Index altered.
   

4. 
   

5. SQL> alter index IND_ACCOUNT_ID rebuild online;
   

6. 
   

7. Index altered.

8.檢查索引狀態(tài)，此時索引恢復(fù)正常可用狀態(tài)

1. SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
   

2. 
   

3. INDEX_NAME                     TABLE_NAME                     STATUS
   

4. ------------------------------ ------------------------------ --------
   

5. PK_ID                          TEST_PARTAS VALID
   

6. IND_ACCOUNT_ID                 TEST_PARTAS VALID

9.用truncate 刪除p1分區(qū)數(shù)據(jù)，增加update index參數(shù)

1. SQL> alter table test_partas truncate partition p1 update indexes;
   

2. 
   

3. Table truncated.

10.檢查索引狀態(tài)，此時索引正?？捎脿顟B(tài)

1. SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
   

2. 
   

3. INDEX_NAME                     TABLE_NAME                     STATUS
   

4. ------------------------------ ------------------------------ --------
   

5. PK_ID                          TEST_PARTAS VALID
   

6. IND_ACCOUNT_ID                 TEST_PARTAS VALID

三、擴展

       通過這個問題，我們再擴展一下，如果drop分區(qū)會不會同樣影響索引，答案是肯定的，刪除分區(qū)，索引仍然失效。

1. SQL> alter table test_partas drop partition SYS_P1611;
   

2. 
   

3. Table altered.
   

4. 
   

5. SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
   

6. 
   

7. INDEX_NAME                     TABLE_NAME                     STATUS
   

8. ------------------------------ ------------------------------ --------
   

9. PK_ID                          TEST_PARTAS UNUSABLE
   

10. IND_ACCOUNT_ID                 TEST_PARTAS UNUSABLE

以上是“Partition table分區(qū)表刪除分區(qū)數(shù)據(jù)時導(dǎo)致索引失效怎么辦”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注億速云行業(yè)資訊頻道！