看了ITPUB出的9I优化中《如何给Large Delete操作提速近千倍？》，今天做了个测试。
需求：删除表serviceinfo中sellername重复的记录，LOGINID为主键
服务器：IBM336 1G内存(少了点)
数据量：
SQL> select count(*) from serviceinfo;
COUNT(*)
----------
827275
--------将重复记录插入TMP表
create table serviceinfo_bak as select * from serviceinfo;
--建立临时表
create table serviceinfo_TMP as select * from serviceinfo where rownum<1;
create index IDX_LCT2 on serviceinfo_TMP (LOGINID) tablespace GOU_IDX;
--原表增加索引
create index idx_serviceinfo_name on serviceinfo (sellername) tablespace GOU_IDX;
--插入要删除的重复记录
insert into serviceinfo_tmp
select a.*
from serviceinfo a
where rowid not in (select max(rowid)
from serviceinfo b
where a.sellername = b.sellername);
180527 rows inserted
Executed in 48.312 seconds
commit;
临时表数据量
SQL> select count(*) from serviceinfo_tmp;
COUNT(*)
----------
180527
一、测试直接用一个语句删除
SQL> delete from serviceinfo where serviceinfo.loginid in (select loginid from serviceinfo_tmp);
180527 rows deleted
Executed in 247.297 seconds
二、使用优化方法
--建立删除过程del_serviceinfo_seg、del_serviceinfo_all
create or replace procedure del_serviceinfo_seg as
--1、分段删除，每次10000条
--2、使用BULK COLLECT子句，提高SELECT性能
--3、使用FORALL子句，提高DML性能
type ridArray is table of rowid index by binary_integer;
type dtArray is table of varchar2(50) index by binary_integer;
v_rowid ridArray;
v_mid_to_delete dtArray;
begin
select loginid, rowid bulk collect
into v_mid_to_delete, v_rowid
from serviceinfo_tmp
where rownum < 10001;
forall i in 1 .. v_mid_to_delete.COUNT
delete from serviceinfo where loginid = v_mid_to_delete(i);
forall i in 1 .. v_rowid.COUNT
delete from serviceinfo_tmp where rowid = v_rowid(i);
end;
/
create or replace procedure del_serviceinfo_all as
--循环删除所有记录
i number;
begin
select count(*) into i from serviceinfo_tmp;
while i > 0 loop
begin
EXECUTE IMMEDIATE 'begin del_serviceinfo_seg;end;';
commit;
i := i - 10000;
end;
end loop;
--最后一次删除，删除不足10000条的记录
EXECUTE IMMEDIATE 'begin del_serviceinfo_seg;end;';
commit;
end;
/
SQL> exec del_serviceinfo_all;
PL/SQL procedure successfully completed
Executed in 186.11 seconds
说明：数据量不大时，优化的效果不太明显 247.297 seconds -- 186.11 seconds 提高了约25%
由于服务器性能的限制，没有再进一步测试，相信数据量越大，优化的效果越明显。
