bulk批量删除数据

bulk批量删除数据

1.  案列介绍

需要在一个1亿行的大表中，删除1千万行数据

需求是在对数据库其他应用影响最小的情况下，以最快的速度完成

如果业务无法停止的话，可以参考下列思路：

根据ROWID分片、再利用Rowid排序、批量处理、回表删除

在业务无法停止的时候，选择这种方式，的确是最好的

一般可以控制在每一万行以内提交一次，不会对回滚段造成太大压力

我在做大DML时，通常选择一两千行一提交

选择业务低峰时做，对应用也不至于有太大影响

2.  代码实现

测试环境

drop table t_emp purge;

create table t_emp as select * from emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

insert into t_emp select * from t_emp;

commit;

具体代码

version 1.0

declare

 cursor c_rowid is

   select rowid from t_emp where deptno = 30 order by rowid; --data need to be deleted

 type type_rowid is table of rowid index by pls_integer;

 v_tab_rowid type_rowid;

 v_num       number := 0;

begin

 open c_rowid;

 loop

   fetch c_rowid bulk collect

     into v_tab_rowid limit 50;

   --exit when c_rowid%notfound;

   forall i in v_tab_rowid.first .. v_tab_rowid.last

     delete from t_emp where rowid = v_tab_rowid(i);

   commit;

   v_num := v_num + v_tab_rowid.count;

    exit when c_rowid%notfound;

  endloop;

 close c_rowid;

 dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd:') || 'delete rows '||

                       to_char(v_num));

end;

/

version 2.0

declare

 cursor c_rowid is

   select rowid from t_emp where deptno = 30 order by rowid; --data need tobe deleted

 type type_rowid is table of rowid index by pls_integer;

 v_tab_rowid type_rowid;

 v_num       number := 0;

begin

 open c_rowid;

 loop

   fetch c_rowid bulk collect

     into v_tab_rowid limit 50;

    exit when v_tab_rowid.count=0;

   forall i in v_tab_rowid.first .. v_tab_rowid.last

     delete from t_emp where rowid = v_tab_rowid(i);

   commit;

   v_num := v_num + v_tab_rowid.count;

  endloop;

 close c_rowid;

 dbms_output.put_line(to_char(sysdate, 'yyyy-mm-dd:') || 'delete rows '||

                       to_char(v_num));

end;

/