小編給大家分享一下Partition table分區表刪除分區數據時導致索引失效怎么辦，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
一、描述
有個小任務就是要刪除些數據，哈哈，先自己小開心一下。因為要刪除的數據表是我之前轉換成的分區表。這個分區表是按照里面有個創建時間字段來分區的，1個季度為1個分區。所以我現在要將2017年7月1日之前的數據刪除（數據量約1000萬），可以直接刪除表分區數據就好。如果要是用delete去刪除這么多的數據，我還要寫存儲過程，分批提交的這樣做。就是這樣的一簡單的truncate partition 引發了后繼的業務故障。最終查詢到該表的索引失效，重建立后恢復。真是汗！
二、實驗
1.創建環境
SQL> create table TEST_PARTAS (id number(11), ACCOUNT_ID number(11) ,CTIME date)
2 partition by range (CTIME)
3 interval( NUMTOYMINTERVAL(3,'month'))
4 (partition P0 values less than (TO_DATE('2016-01-01','yyyy-mm-dd')),
5 partition p1 values less than (to_date('2017-01-01','yyyy-mm-dd')));
Table created.
SQL> insert into TEST_PARTAS select t.id,t.account_id,t.create_time from act_test t;
3483178 rows created.
SQL> commit;
Commit complete.
SQL> EXEC SYS.DBMS_STATS.GATHER_TABLE_STATS('SAM','TEST_PARTAS');
PL/SQL procedure successfully completed.
2.檢查分區表及數據
SQL> select count(*) from TEST_PARTAS;
COUNT(*)
----------
3483178
SQL> set lines 120 pages 200;
SQL> set long 9999999
SQL> col table_name for a15
SQL> col PARTITION_NAME for a10
SQL> select t.table_name,t.partition_name,t.num_rows,t.blocks,t.interval,t.high_value from USER_TAB_PARTITIONS t;
TABLE_NAME PARTITION_ NUM_ROWS BLOCKS INT HIGH_VALUE
--------------- ---------- ---------- ---------- --- --------------------------------------------------
TEST_PARTAS P0 2182116 6046 NO TO_DATE(' 2016-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PARTAS P1 616290 36506 NO TO_DATE(' 2017-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PARTAS SYS_P1611 44829 4030 YES TO_DATE(' 2017-04-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PARTAS SYS_P1612 21706 3022 YES TO_DATE(' 2017-07-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PARTAS SYS_P1613 172525 3022 YES TO_DATE(' 2017-10-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PARTAS SYS_P1614 442435 2014 YES TO_DATE(' 2018-01-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
TEST_PARTAS SYS_P1615 3277 238 YES TO_DATE(' 2018-04-01 00:00:00', 'SYYYY-MM-DD HH24:
MI:SS', 'NLS_CALENDAR=GREGORIAN')
7 rows selected.
3.創建主鍵和索引
SQL> alter table TEST_PARTAS add constraint pk_id primary key(ID);
Table altered.
SQL> CREATE INDEX IND_ACCOUNT_ID ON TEST_PARTAS (ACCOUNT_ID);
Index created.
4.檢查索引狀態，當前狀態可用
SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID
5.用truncate 刪除p0分區數據，不加update index參數
SQL> alter table test_partas truncate partition p0;
Table truncated.
6.檢查索引狀態，狀態不可用
SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS UNUSABLE
IND_ACCOUNT_ID TEST_PARTAS UNUSABLE
7.重建立索引，要加online ，盡量減小對業務的沖擊
SQL> alter index PK_ID rebuild online;
Index altered.
SQL> alter index IND_ACCOUNT_ID rebuild online;
Index altered.
8.檢查索引狀態，此時索引恢復正?？捎脿顟B
SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID
9.用truncate 刪除p1分區數據，增加update index參數
SQL> alter table test_partas truncate partition p1 update indexes;
Table truncated.
10.檢查索引狀態，此時索引正?？捎脿顟B
SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS VALID
IND_ACCOUNT_ID TEST_PARTAS VALID
三、擴展
通過這個問題，我們再擴展一下，如果drop分區會不會同樣影響索引，答案是肯定的，刪除分區，索引仍然失效。
SQL> alter table test_partas drop partition SYS_P1611;
Table altered.
SQL> select T.INDEX_NAME,T.TABLE_NAME,T.STATUS from user_indexes t where t.table_name='TEST_PARTAS';
INDEX_NAME TABLE_NAME STATUS
------------------------------ ------------------------------ --------
PK_ID TEST_PARTAS UNUSABLE
IND_ACCOUNT_ID TEST_PARTAS UNUSABLE
以上是“Partition table分區表刪除分區數據時導致索引失效怎么辦”這篇文章的所有內容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內容對大家有所幫助，如果還想學習更多知識，歡迎關注億速云行業資訊頻道！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
