要在一个1亿行的大表中,删除1千万行数据,在对数据库其他应用影响小的情况下,以快的速度完成,那么操作期间应该注意什么?如果中途中断了,有木有方法再继续?如果应用不可停,又该怎么办呢?

  删除了10分之一的数据,索引可能还需重建,统计信息可能也得更新。由于昂贵的delete操作,Undo空间亚历山大、Redo log 也要担心被撑爆,而且批量delete虽然影响小,但是可能会造成大量的空数据块。如此种种,剪不断、理还乱、是删除,别是一番滋味在心头啊。

  很显然,删除或更新的方法和数据库的设计及软硬件的配置有重大关系。不同的数据库设计,采用的方案必然不同,其所谓的因库制宜。在操作之前,简单也是重要的事情,那是作个有效的备份。无论什么情况下,只要对数据库做变更,都不别忘了有效的备份。

  如果条件允许,操作前好做个有效的全备;如果不允许,你起码要备份你要操作的对象和数据;如果不备份,那便是在钢丝上耍,而且下面是万丈深渊。

  思路一:

  分区表方案的情况讨论。这种想法是基本的,也是后面几种思路的底韵。

  差的一种方法大概是:

  delete from xxx where xxx=xxx

  下班前提交这样一个语句,估计晚上觉都睡不踏实,满脑子都是脚本运行的情况以及可能出现的错误。

  如果是分区表,要删掉的数据正好在分区之内,那简单了,直接truncate partition,省心省力。

  对于1亿的数据量,传统的bulk delete,forall,for loop等等,效果都不太可观,可分而治之。

  可行的方案是:

  1、做分区表,逐个分区处理。

  如果表未分区又有足够的磁盘空间,可用在线重定义的方法,把表转成分区表。然而如果业务不可停,在线重定义严重影响数据库正常使用的,好是晚上干活。

  2、接着按照表里面的条件,大概100000条记录一个循环,处理完立即提交。

  3、如果磁盘很大,可以用磁盘换性能,直接用insert替代delete,把满足条件的行记录,插入到新表。

  insert的时候还是要用循环,分十万~百万条记录左右,提交一次。

  思路二: CTAS + RENAME

  因为要在对数据库及其他应用影响小的情况下做,如果直接对表进行删除则会出现应用因为等待数据释放锁而等待;而且常规的DELETE语句,会生成大量的UNDO和REDO;而且表上如果有大量索引的话,导致删除还会更慢;而且可能会造成大量的空数据块,如果有扫描大量行的操作,空块太多,也会导致性能下降。如果可以停止业务,防止对此表的更新,那么CTAS + RENAME也是个不赖的选择。

  这种方法只适用于没有业务发生的情况,对于大多数OLTP系统还是不适用的。

  由于CTAS是DDL语句,基本没有太多UNDO的生成,如果CTAS失败了,重新在执行一遍即可。

  大致过程如下:

  比如:对象表为test_01,我想要删去日期(deal_date)为20120301的数据

  ① CTAS的方法创建一个新表,建表条件为删除条件的对立条件,可考虑nologging+并行

  例如:create table test_02 as select * from test_01 where deal_Date<>'20120301'

  ② 在新表上建立相关索引和约束

  ③ 重命名新表为老表名字

  例如:ALTER TABLE test_02 RENAME TO test_01

  ④ 如果没问题 删除老表

  ⑤ 后查看和该表相关的存储过程、函数等,重新编译

  如果用CTAS在建表的时候会对原表建立一致性读的,如果这时候对原表有插入或更新,需要访问undo表空间然后完成一致读,而且一旦失败需要全部回滚,数据量这么大,这样做的风险是巨大的。

  我们可以访问dba_extents视图,通过rowid将原表划分成许多不重叠的区域,然后起多个job,job中通过rowid访问然后删除数据。删除完后的区域写日志,这样即使中途失败通过划分的rowid区域和日志都是可以重启的,而且速度要比bulkcollect好,因为bulk collect是缓存100到200条一delete,一个区域直接一个delete。但是这种方式好需要先在表上加读取锁,不然有insert和update会破坏原来的rowid划分。

  这种通过rowid的思路,我会在下一篇博客继续介绍。