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

林周ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為成都創(chuàng)新互聯(lián)的ssl證書銷售渠道，可以享受市場價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：13518219792（備注：SSL證書合作）期待與您的合作！

1.  案列介紹

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

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

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

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

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

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

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

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

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

測試環(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;

/