Oracle删除大量数据的实践

前端之家收集整理的这篇文章主要介绍了Oracle删除大量数据的实践前端之家小编觉得挺不错的,现在分享给大家,也给大家做个参考。
一、引言
  从来没有想过,删除表中的数据都会成为问题。但是,当表中的数据量非常大时,删除数据会成为一个不小的问题。
  这里简单介绍一下我本人遇到的一个小问题及解决过程,仅讨论过程,不涉及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中,使用@文件名,执行命令文件 原文链接:https://www.f2er.com/oracle/211905.html

猜你在找的Oracle相关文章