创建过程删除大量数据的方法

背景：

18:01:06 SQL>  select count(*) from suhistor_bak;

  COUNT(*)
----------
  73809978

18:06:48 SQL> select count(*) from i26611_suhistor;

  COUNT(*)
----------
    342482

已用时间:  00: 00: 00.02

 

删除下面数据需要20分钟以上：

delete from suhistor_bak where shshnr in (select shshnr fromi26611_suhistor) and rownum < 100000;

 

CREATE OR REPLACE procedure BUSINESSDATA_LIVE.del_all as
i number;
ii number;
begin
select count(*) into i from i26611_suhistor ;
while i>0 loop
begin
execute immediate 'Begin del_suhistor;end;';
commit;
i:=i-10000;
end;
ii:=400000-i;
dbms_output.put_line(ii||'行删除');
end loop;
-- delete the rest
execute immediate 'begin del_suhistor;end;';
commit;
end;
/

CREATE OR REPLACE procedure BUSINESSDATA_LIVE.del_suhistor as
type rid is table of rowid index by binary_integer;
type dt is table of varchar2(50) index by binary_integer;
v_rowid rid;
v_dt dt;
begin
select shshnr,rowid bulk collect into v_dt,v_rowid from i26611_suhistor where rownum<10000;
forall i in 1..v_dt.count
delete from suhistor_bak where shshnr=v_dt(i);
forall i in 1..v_rowid.count
delete from i26611_suhistor where rowid=v_rowid(i);
end;
/

执行过程删除：

2分钟左右搞定：

18:12:12 SQL> exec BUSINESSDATA_LIVE.del_all

PL/SQL 过程已成功完成。

已用时间:  00: 02: 17.65