Oracle删除大量数据的实践
发布时间：2020-12-12 15:46:01 所属栏目：百科 来源：网络整理
导读：一、引言 从来没有想过，删除表中的数据都会成为问题。但是，当表中的数据量非常大时，删除数据会成为一个不小的问题。 这里简单介绍一下我本人遇到的一个小问题及解决过程，仅讨论过程，不涉及SQL、存储过程的写法。方法很简单，高手绕行。 二、场景 运行了
|
一、引言
从来没有想过，删除表中的数据都会成为问题。但是，当表中的数据量非常大时，删除数据会成为一个不小的问题。 这里简单介绍一下我本人遇到的一个小问题及解决过程，仅讨论过程，不涉及SQL、存储过程的写法。方法很简单，高手绕行。 二、场景 运行了一年多的生产库，每小时大约五万条数据，总数据量超过四亿。今天发现有一个星期程序出了问题，整整一个星期的数据是重复的。需要把重复的数据删除。 三、解决过程 （一） 删除重复数据的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中，使用@文件名，执行命令文件。 （编辑：李大同） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |