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;

/