Oracle批量删除过期数据

Oracle批量删除过期数据

1 前言

​ 在这个月的开发中，我做的一个需求是删除某个表中半年前逻辑删除的数据。而生产上一张表的数据量是千万级别的，要清理的数据大概在百万级别。

2 初版

开始我觉得这个需求挺简单的，脑海里一下子浮现下面这条语句

delete from xxx t where t.delete_flag=`Y` and t.last_updated_date< addmonth(sysdate,-6);

但是后面在我测试的时候，弊端一下子就暴露出来了，删除5000条用时90秒。。。。效率太低了，并且这种写法还有一个很严重的缺陷，就是它会将所有符合删除条件的记录全部锁定（行锁），让其他人无法操作这些数据。这种写法直接给pass掉了。

3 升级版

升级版主要是为了解决符合删除条件的记录全部锁定的问题，所以引入了存储过程，循环清理数据，一次删除5000条，sql大概如下所示

DECLEAR
INC NUMBER;
BEGIN
SELECT COUNT(1) INTO INC FROM xxx t WHERE t.delete_flag=`Y` AND t.last_updated_date< addmonth(sysdate,-6);
FOR I IN 1..(INC/5000)+1 LOOP
DELETE FROM xxx t WHERE t.delete_flag=`Y` AND t.last_updated_date< addmonth(sysdate,-6) AND ROWNUM<5000;
END LOOP;
END;

但是这种也有很明显的缺陷。多次delete，而每次delete均会进行全表扫描，耗时太长。为了解决这个问题，请教同事，发现原来还有游标这个东西，用上这个，效率一下高一截。

4 最终版

原理：利用游标暂存所有删除记录的主键id，然后根据主键删除，

根据主键删除，用上了主键索引，比上个版本每次delete都全表扫描，效率上不知道高出多少。。。

DECLEAR
	--声明一个主键的集合类型
	TYPE ids_tbl IS TABLE OF xxx.id%TYPE;
	id_list ids_tbl;
	INC NUMBER;
	LEN NUMBER:=5000;

	--创建游标
	COURSOR cur_cdd IS SELECT t.id FROM xxx t WHERE t.delete_flag=`Y` AND t.last_updated_date< addmonth(sysdate,-6);
BEGIN
--打开游标
OPEN cur_cdd;
LOOP
	--游标中的值取出来填充到id_list中，最多填充5000个
	FETCH cur_cdd 
	BULK COLLECT INTO id_list
	LIMIT LEN;
	
	-- 循环5000次，根据主键清理数据
	FORALL i IN 1..id_list.count      
		DELETE FROM xxx t WHERE t.id=`Y`;
	--每5000条提交一次事务
    commit;
    --游标中取不出来东西的时候，结束循环
    EXIT WHEN cur_cdd%NOTFOUND
END LOOP;
--关闭游标
CLOSE cur_cdd;
dbms_output.put_line('finished!');
--异常处理
exception
   when no_data_found then 
    dbms_output.put_line('未找到数据');
END;

这个存储过程已经是比较完善的

• 可重复执行，需要清理数据的时候，直接运行该脚本就行
• 效率高，全表扫描一次，具体删除根据主键删，用到索引，删除速度很快

接下来补充一下游标相关的知识点

游标的属性:

%isopen 是否打开 boolean类型

%rowcount 影响的行数 不是总行数，例如总数100，已经取了10条，那么这个数为10

%found 是否找到 boolean类型

%notfound 是否没找到 boolean类型