【Partition table】分区表删除分区数据时，导致索引失效

一、描述

       今天有个小任务就是要删除些数据，哈哈，先自己小开心一下。因为要删除的数据表是我之前转换成的分区表。这个分区表是按照里面有个创建时间字段来分区的，1个季度为1个分区。所以我现在要将2017年7月1日之前的数据删除（数据量约1000万），可以直接删除表分区数据就好。如果要是用delete去删除这么多的数据，我还要写存储过程，分批提交的这样做。就是这样的一简单的truncate partition 引发了后继的业务故障。最终查询到该表的索引失效，重建立后恢复。真是汗！

二、实验

1.创建环境

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.检查分区表及数据

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.创建主键和索引

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.检查索引状态，当前状态可用

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分区数据， 不加update index参数

1. SQL> alter table test_partas truncate partition p0;
   
2. 
   
3. Table truncated.

6.检查索引状态，状态不可用 

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 ，尽量减小对业务的冲击

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.检查索引状态，此时索引恢复正常可用状态

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分区数据， 增加update index参数

1. SQL> alter table test_partas truncate partition p1 update indexes;
   
2. 
   
3. Table truncated.

10.检查索引状态，此时索引正常可用状态

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分区会不会同样影响索引，答案是肯定的，删除分区，索引仍然失效。

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

四、总结

       一个小小的失误，带来了大大的问题，还好这次操作，影响的不是核心业务表。通过失误，也让我看到了自己对知识点掌握上的不足。以后的路还很远，振作起来，努力学习吧。让自己在后面的DB生涯中，少范错误，多多提高效率。没有什么比学习与进步更快乐，In the end, Happy spring festival 2018 to everyone.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26148431/viewspace-2151041/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26148431/viewspace-2151041/