DELETE大批量数据的性能优化

http://litterbaby.itpub.net/post/16841/276327

 

DELETE大批量数据的性能优化

问题的提出：

一个表有上千万的数据，欲从该表中删除部分数据；

在线用的生产库，不能影响生产；

该表有四个索引，删除的sql语句用到了索引；

正常业务不能停止－不能disable约束，也不能临时不用索引；

问题的解决：

这个问题中应该重点考虑的问题是这个系统是生产系统，不能离线，所以只能是根据情况考虑一些在线的删除方式。

1、 如果是定期清楚历史数据的话,可以考虑用partition的方式，采用分区方式是其优点之一，能够使用其易于管理的一个方面，在这里是不是应该考虑分区时候是采用我们需要删除的数据为分区的原则。

2、 增大回滚段，通过设置使用这个回滚段，来提高系统的删除操作的速度，但是对于Oracle9i以后的系统来说，由于使用了自动管理回滚段的情况，如何进行回滚段的设置。

3、 增大redo log在线文件的大小，以便减少checkpoint的执行频率，增大buffer来提高删除的速度。这个主要目的是减少系统checkpoint的次数，尽量少减少磁盘IO的次数，能够在数据缓存上执行的尽量在内存中实现。

4、 使用rowid来删除数据。

1．根据用户所提需求过滤需要删除的源数据，create 临时表 as select rowid rid from 需要删除的表 where 删除条件。

2．使用pl/sql block来删除数据，这样能保证及时递交，防止lock过多的行导致系统负载增加。这里需要注意commit的频率，一般为更新100条记录commit一次。

create table temp_del表名_040803 as select rowid rid from 要删除的表 where ...... declare execrow number; begin execrow:=1; for i in 1..需要更新的记录数/100(取整) loop delete 要删除的表 where rowid in(select rid from temp_del表名_040803 where rownum<100); delete from temp_del表名_040803 where rownum<100; commit; end loop; end; /

记得再建一张临时表保存要删除的内容已备回滚。这样根据rowid来删除的话效率会比较高。

5、 在Oracle 9i之后，可以考虑使用bulk delete的新功能。（这个没有高清楚是什么东西）

6、 如果在不是生产库的时候，如果索引没有在删除时使用的情况下，（如果在删除的语句中使用了索引得话，这样能够提高删除的速度）可以将索引删除，等执行完删除语句的时候，可以考虑重建索引，因为删除索引上的数据的时候也是需要时间和系统消耗的，这时候需要看看你所要删除的数据量的多少，如果数据量相对整个表比较大的时候，可以考虑使用全表扫描，这样应该是更快的方式，如果比率比较小的时候，使用索引找到rowid来删除应该是比较好的选择。

7、 有人提出这样的观点：

相关问题，俺们也经常遇到，共享经验如下： 1.表分区异常重要 2.索引过多很是麻烦 3.相关约束更是头痛（4M数据如果有N个子表对其参照，乖乖..) 4.表空间如过是dictionary管理，必须选择分段批量提交(俺们的数据库都被锁死了） 5.会滚、排序,大redo,多个arch线程，必须养成习惯，任何大数据量批量操作，都不可少。 6.临时表（在临时表空间上）用于查询果然较快。 7.引用偶像的话“Delete就是慢,就是慢,就是慢.”，没办法。 最后还是想说，象这样的工作只有考虑应用的特征，才会找到比较好的方法（俺觉得）

问题：表空间如过是dictionary管理，必须选择分段批量提交(俺们的数据库都被锁死了），为什么是这样的？

 

==========================

http://www.oracle.com.cn/viewthread.php?tid=26214&extra=&page=1

 

======================

http://blog.csdn.net/lunar2000/archive/2005/06/27/404090.aspx

 

========================