共计 4213 个字符，预计需要花费 11 分钟才能阅读完成。
丸趣 TV 小编给大家分享一下 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. 检查索引状态，此时索引恢复正常可用状态
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. 检查索引状态，此时索引正常可用状态
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 分区表删除分区数据时导致索引失效怎么办”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注丸趣 TV 行业资讯频道！
