Oracle批量删除过期数据

逃离我推掉我的手 2022-10-19 13:59 169阅读 0赞

# `Oracle`批量删除过期数据 #

### 文章目录 ###

*  \`Oracle\`批量删除过期数据
 *  1 前言
 *  2 初版
 *  3 升级版
 *  4 最终版

# 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`类型