共計(jì) 4213 個字符，預(yù)計(jì)需要花費(fèi) 11 分鐘才能閱讀完成。
丸趣 TV 小編給大家分享一下 Partition table 分區(qū)表刪除分區(qū)數(shù)據(jù)時導(dǎo)致索引失效怎么辦，相信大部分人都還不怎么了解，因此分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后大有收獲，下面讓我們一起去了解一下吧！
一、描述
有個小任務(wù)就是要刪除些數(shù)據(jù)，哈哈，先自己小開心一下。因?yàn)橐獎h除的數(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ù)。真是汗！
二、實(shí)驗(yàn)
1. 創(chuàng)建環(huán)境
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. 檢查分區(qū)表及數(shù)據(jù)
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. 創(chuàng)建主鍵和索引
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. 檢查索引狀態(tài)，當(dāng)前狀態(tài)可用
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 分區(qū)數(shù)據(jù)，不加 update index 參數(shù)
SQL alter table test_partas truncate partition p0;
Table truncated.
6. 檢查索引狀態(tài)，狀態(tài)不可用
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，盡量減小對業(yè)務(wù)的沖擊
SQL alter index PK_ID rebuild online;
Index altered.
SQL alter index IND_ACCOUNT_ID rebuild online;
Index altered.
8. 檢查索引狀態(tài)，此時索引恢復(fù)正常可用狀態(tài)
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 分區(qū)數(shù)據(jù)，增加 update index 參數(shù)
SQL alter table test_partas truncate partition p1 update indexes;
Table truncated.
10. 檢查索引狀態(tài)，此時索引正常可用狀態(tài)
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
三、擴(kuò)展
通過這個問題，我們再擴(kuò)展一下，如果 drop 分區(qū)會不會同樣影響索引，答案是肯定的，刪除分區(qū)，索引仍然失效。
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 分區(qū)表刪除分區(qū)數(shù)據(jù)時導(dǎo)致索引失效怎么辦”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家都有了一定的了解，希望分享的內(nèi)容對大家有所幫助，如果還想學(xué)習(xí)更多知識，歡迎關(guān)注丸趣 TV 行業(yè)資訊頻道！
