189 8069 5689

Partitiontable分区表删除分区数据时导致索引失效怎么办

小编给大家分享一下Partition table分区表删除分区数据时导致索引失效怎么办，相信大部分人都还不怎么了解，因此分享这篇文章给大家参考一下，希望大家阅读完这篇文章后大有收获，下面让我们一起去了解一下吧！

成都创新互联是创新、创意、研发型一体的综合型网站建设公司，自成立以来公司不断探索创新，始终坚持为客户提供满意周到的服务，在本地打下了良好的口碑，在过去的10多年时间我们累计服务了上千家以及全国政企客户，如成都iso认证等企业单位，完善的项目管理流程，严格把控项目进度与质量监控加上过硬的技术实力获得客户的一致赞扬。

一、描述

       有个小任务就是要删除些数据，哈哈，先自己小开心一下。因为要删除的数据表是我之前转换成的分区表。这个分区表是按照里面有个创建时间字段来分区的，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')));

  7. Table created.

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

  10. 3483178 rows created.

  12. SQL> commit;

  13. Commit complete.

  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;

  3.   COUNT(*)

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

  5.    3483178

  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

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

  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')

  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')

  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')

  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')

  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')

  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')

  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')

  38. 7 rows selected.


3.创建主键和索引

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

  2. Table altered.

  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';

  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;

  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';

  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;

  3. Index altered.

  5. SQL> alter index IND_ACCOUNT_ID rebuild online;

  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';

  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;

  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';

  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;

  3. Table altered.

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

  7. INDEX_NAME                     TABLE_NAME                     STATUS

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

  9. PK_ID                          TEST_PARTAS UNUSABLE

  10. IND_ACCOUNT_ID                 TEST_PARTAS UNUSABLE

以上是“Partition table分区表删除分区数据时导致索引失效怎么办”这篇文章的所有内容，感谢各位的阅读！相信大家都有了一定的了解，希望分享的内容对大家有所帮助，如果还想学习更多知识，欢迎关注创新互联行业资讯频道！


新闻标题：Partitiontable分区表删除分区数据时导致索引失效怎么办
标题链接：http://cdxtjz.com/article/jdjcjc.html

其他资讯