Oracle删除大量数据的实践

悠悠 2022-04-11 08:50 492阅读 0赞

\[b\]一、引言\[/b\]  
　　从来没有想过，删除表中的数据都会成为问题。但是，当表中的数据量非常大时，删除数据会成为一个不小的问题。  
　　这里简单介绍一下我本人遇到的一个小问题及解决过程，仅讨论过程，不涉及SQL、存储过程的写法。方法很简单，高手绕行。  
  
\[b\]二、场景\[/b\]  
　　运行了一年多的生产库，每小时大约五万条数据，总数据量超过四亿。今天发现有一个星期程序出了问题，整整一个星期的数据是重复的。需要把重复的数据删除。  
  
\[b\]三、解决过程\[/b\]  
　　（一）  
　　删除重复数据的SQL很简单，用ROWID来排除就可以了，最开始想到的方法是使用一个SQL来解决问题。很快写出来，在测试库上验证成功。  
　　打开SQL Plus，执行删除SQL。结果执行了一天多，都没有执行完，而且由于时间太长，SQL Plus与服务器的连接已经断开了。  
　　不知是连接超时导致SQL没有执行完，还是执行完了连接断开事务没有提交而回滚了。专门去查了一下如何控制连接发呆时间，ORACLE有个参数可以控制，IDLE\_TIME，但查到的资料说，长查询时，连接不会断开（事实证明好像不是这样，有点儿奇怪，难道执行时间很长的删除与长查询不一样？？？）修改IDEL\_TIME会对系统运行有一定的影响，我决定还是不改它，想办法优化删除的方法。  
　　（二）  
　　接下来想到的方法是，先把重复的数据记录的ROWID找到，保存到一个临时表中，然后根据这个表使用存储过程来删除，每一万条提交一次数据，防止过多的UNDO数据(Oracle不支持避免生成UNDO日志的hint，应该是出于安全的考虑吧)，提高效率，同时避免连接超时，事务回滚。  
　　结果也是执行了一天多，没有结果，连接超时。  
　　（三）  
　　由于存储过程执行完成，才会显示过程中dbms\_output.put\_line输出的信息，专门建了个表记录存储过程执行过程中的调试信息。发现将重复数据的ROWID找到、插入临时表的时间特别长。  
　　把查找重复数据的范围进行缩减，调整为对一个小时的数据进行查找、使用批量操作（bulk collect into，forall）的方法来删除。在生产库上进行了测试，大约5分钟执行完毕，效果不错。  
　　再写一个存储过程，依次执行按小时删除的存储过程。也就是把按小时删除的过程打包成一个大的存储过程，一次性调用。结果还是没有执行成功，连接超时。  
　　（四）  
　　看来这个连接超时还真是个问题，可能是由于连接超时导致操作失败。但我每个小存储过程中都有提交操作，那也应该能完成部分数据的删除操作呀？事实上是没有任何数据被删除。这个问题我没有想通。  
　　最后只好使用一个简单粗暴的方法：  
　　exec delete\_by\_hour('2016101400');  
　　exec delete\_by\_hour('2016101401');  
　　exec delete\_by\_hour('2016101402');  
　　……  
　　写了大量这样的调用过程，复制到剪贴板中，然后粘到SQL Plus中，这些命令乖乖地一个接一个地执行，最后顺利地删除了所有数据，一共用了13个小时。  
　　当然也可以将上面的内容保存在文本文件中，在SQL Plus中，使用@文件名，执行命令文件。