一种批量删除数据的方法

本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一种批量删除数据的方法

这两天碰见一个比较紧急的生产问题,由于还在处理中,所以暂时不能给出整体描述,但其中涉及的一个问题就是删除一张大表中的过期历史数据,针对不同的类型的表可能有不同的解决方法,比如若是按照时间做的分区表,drop partition删除分区的操作可能是效率最快的、最简单的,若是一张普通表则需要有一些索引键值为删除条件,但需要注意的是最好做批量删除,且一次删除量不要太多,因为delete操作会将数据前镜像保存在UNDO回滚表空间,由于占用过多、事务过大、执行时间过长、UNDO空间过小等一系列问题存在,就有可能会影响正常的交易操作,这话题不是今天的主题。

删除历史数据可以使用存储过程,也可以写一个程序来做,区别是存储过程是直接在数据库中操作,少了客户端和数据库交互的环节,若是需要一些复杂的校验逻辑,可能写程序要更方便一些,但也不是绝对的,可能有人认为存储过程更好,无论什么方式,能解决问题才是最重要。

eygle大神曾经提供过一个用于批量删除数据的存储过程,在这引用下,版权还是eygle的:),(),强调的就是:分批删除,逐次提交。


create or replace procedure delBigTab( p_TableName       in    varchar2, p_Condition       in    varchar2, p_Count        in    varchar2 )
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE'delete from '||p_TableName||' where '||p_Condition||' and rownum = :rn'USING p_Count;if SQL%NOTFOUND thenexit;elsen_delete:=n_delete + SQL%ROWCOUNT;end if; commit;
end loop; commit; DBMS_OUTPUT.PUT_LINE('Finished!'); DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');end; /

这是一可以有参数输入的存储过程,分别是:
p_TableName:待删除表的表名,
p_Condition:删除条件,
p_Count:一次删除的记录条数,rownum,
而且用了自治事务pragma autonomous_transaction,存储过程使用commit结尾。
整个逻辑很清晰和透彻,想必各位稍微看看都能明白。

这篇文章中()还介绍了另一种更精细的方法,判断日志是否已经归档了,避免数据删除快于日志归档的速度,如果发现尚未完成切换,则sleep一下,等待切换完成,再做下一次删除。

针对我这个需求,有一些可以改动的地方,由于这张表是一个按照NUMBER值做hash的哈希分区表,所以从效率上看,还可以精确至每个hash分区来做删除,这点是建荣给的建议,另外例子中自治事务我觉得也是可以不用的,因此针对SQL语句可以改为如下:


delete from table partition (p1) where insert_timesysdate and rownum = :rn;

即指定分区名称(这可以作为另一个参数),然后可以通过手工执行,依次用rn=100、1000、5000、10000等几个值来选择从时间和删除量可接受的范围。总结一下,
1.如果使用存储过程,或许可以不用自治事务。
2.可以将partition作为另一个参数。
3.由于这张表数据量太大,即使使用索引条件做count(*)操作时间都很久,因此暂时未知符合条件需要删除的记录条数,因此需要根据测试和时间需求,明确rownum使用的可行条数,选择小值则可能循环次数要多,选择大值则可能循环次数少,总之务必要分批删除、批量提交,避免delete子句对UNDO表空间的过大影响。

以上只是提供了删除历史记录的一种存储过程操作的方法,以及针对我的需求做的一些改进,至于会采用何种方法,可能还会根据得到的信息,有其他需要改进的地方,可能还会使用程序的方法,可能会使用这种存储过程,待完成后会再做总结了。

如果您觉得此篇文章对您有帮助,欢迎关注微信公众号:bisal的个人杂货铺,您的支持是对我最大的鼓励!共同学习,共同进步:)

一种批量删除数据的方法
本人花费半年的时间总结的《Java面试指南》已拿腾讯等大厂offer,已开源在github ,欢迎star!

转载声明:转载请注明出处,本技术博客是本人原创文章

本文GitHub https://github.com/OUYANGSIHAI/JavaInterview 已收录,这是我花了6个月总结的一线大厂Java面试总结,本人已拿大厂offer,欢迎star

原文链接:blog.ouyangsihai.cn >> 一种批量删除数据的方法


 上一篇
两个和安全相关的Oracle参数演进 两个和安全相关的Oracle参数演进
上次分享中介绍了两个和Oracle安全相关的参数,一个是o7_dictionary_accessibility,一个是remote_os_authent,比较小众的知识点,了解即可。 1.o7_dictionary_accessibilit
下一篇 
SYS_CONTEXT函数返回IP地址的一些误解 SYS_CONTEXT函数返回IP地址的一些误解
上次分享中曾使用了SYS_CONTEXT函数获取ip地址,但返回值为空,当时认为其是返回ipv6的地址,所以为空,但其实这是错误的结论。虽然是一个小小的知识点,但从中可以看出Oracle对于这种内置函数的考虑非常周到,我们先看如下是返回空的