bulk批量刪除數(shù)據(jù)

bulk批量刪除數(shù)據(jù)

1.  案列介紹

需要在一個(gè)1億行的大表中，刪除1千萬(wàn)行數(shù)據(jù)

需求是在對(duì)數(shù)據(jù)庫(kù)其他應(yīng)用影響最小的情況下，以最快的速度完成

如果業(yè)務(wù)無法停止的話，可以參考下列思路：

根據(jù)ROWID分片、再利用Rowid排序、批量處理、回表刪除

在業(yè)務(wù)無法停止的時(shí)候，選擇這種方式，的確是最好的

一般可以控制在每一萬(wàn)行以內(nèi)提交一次，不會(huì)對(duì)回滾段造成太大壓力

我在做大DML時(shí)，通常選擇一兩千行一提交

選擇業(yè)務(wù)低峰時(shí)做，對(duì)應(yīng)用也不至于有太大影響

2.  代碼實(shí)現(xiàn)

測(cè)試環(huán)境

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;

/