一描述
今天有个小任务就是要删除些数据, 哈哈, 先自己小开心一下因为要删除的数据表是我之前转换成的分区表这个分区表是按照里面有个创建时间字段来分区的, 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 生涯中, 少范错误, 多多提高效率
来源: http://www.linuxidc.com/Linux/2018-02/150984.htm