Oracle删除大量数据的例子

作者：袖梨 2022-06-29

一、引言
从来没有想过，删除表中的数据都会成为问题。但是，当表中的数据量非常大时，删除数据会成为一个不小的问题。

这里只描述解决过程，不涉及SQL、存储过程的写法。方法很简单，高手绕行。

二、场景
运行了一年多的生产库，每小时大约五万条数据，今天发现有一个星期程序出了问题，整整一个星期的数据是重复的。需要把重复的数据选出来删除。

三、解决过程
（一）

删除重复数据的SQL很简单，用ROWID来排除就可以了，最开始想到的方法是使用一个SQL来解决问题。很快写出来，在测试库上验证成功。

打开SQL Plus，执行删除SQL。结果执行了一天多，都没有执行完，而且由于时间太长，SQL Plus与服务器的连接已经断开了。

不知是不是连接超时导致SQL没有执行完，还是执行完了连接断开事务没有提交而回滚了。专门去查了一下如何控制连接发呆时间，ORACLE有个参数可以控制，IDLE_TIME，但查到的资料说，长查询时，连接不会断开（事实证明好像不是这样，有点儿奇怪，难道执行时间很长的删除与长查询不一样？？？）修改IDEL_TIME会对系统运行有一定的影响，我决定还是不改它，想办法优化删除的方法。

（二）

接下来想到的方法是，先把重复的数据记录的ROWID找到，保存到一个临时表中，然后根据这个表使用存储过程来删除，每一万条提交一次数据，防止过多的UNDO数据，提高效率，同时避免连接超时，事务回滚。

结果也是执行了一天多，没有结果，连接超时。

（三）

由天存储过程执行完成，才会显示过程中dbms_output.put_line输出的信息，专门建了个表记录存储过程执行过程中的调试信息。发现将生重复数据的ROWID找到、插入临时表的时间特别长。

把查找重复数据的范围进行缩减，调整为依次对每个小时的进行查找、使用批量操作（buld collect into，forall）的方法来删除。在生产库上进行了测试，大约5分钟执行完毕，效果不错。

再写一个存储过程，依次执行按小时删除的存储过程。

（四）

结果还是没有执行成功，连接超时。看来这个连接超时还真是个问题，可能是由于连接超时导致操作失败。但我每个小存储过程中都有提交操作，那也应该能完成部分数据的删除操作呀？事实上是没有任何数据被删除。这个问题我没有想通。

最后解决的办法就是，使用一个简单粗暴的方法：

exec delete_by_hour('2016101400');

exec delete_by_hour('2016101401');