一聚教程网:一个值得你收藏的教程网站

热门教程

Oracle删除大量数据的例子

时间:2022-06-29 09:30:39 编辑:袖梨 来源:一聚教程网

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

这里只描述解决过程,不涉及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');

exec delete_by_hour('2016101402');

……

写了大量这样的调用过程,复制到剪贴板中,然后粘到SQL Plus中,这些命令乖乖地一个接一个地执行,最后顺利地删除了所有数据,一共用了13个小时。

热门栏目