一亿行删除或修改一千万行的思路总结
作者:网络转载 发布时间:[ 2012/12/13 10:24:04 ] 推荐标签:
要在一个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的思路,我会在下一篇博客继续介绍。
相关推荐
更新发布
功能测试和接口测试的区别
2023/3/23 14:23:39如何写好测试用例文档
2023/3/22 16:17:39常用的选择回归测试的方式有哪些?
2022/6/14 16:14:27测试流程中需要重点把关几个过程?
2021/10/18 15:37:44性能测试的七种方法
2021/9/17 15:19:29全链路压测优化思路
2021/9/14 15:42:25性能测试流程浅谈
2021/5/28 17:25:47常见的APP性能测试指标
2021/5/8 17:01:11