阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

Oracle分区表删除分区数据时导致索引失效解决

260次阅读
没有评论

共计 4167 个字符,预计需要花费 11 分钟才能阅读完成。

一、描述

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

四、总结
      一个小小的失误,带来了大大的问题,还好这次操作,影响的不是核心业务表。通过失误,也让我看到了自己对知识点掌握上的不足。以后的路还很远,振作起来,努力学习吧。让自己在后面的 DB 生涯中,少范错误,多多提高效率。

更多 Oracle 相关信息见 Oracle 专题页面 https://www.linuxidc.com/topicnews.aspx?tid=12

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计4167字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中