背景:
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