看了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%

由于服务器性能的限制，没有再进一步测试，相信数据量越大，优化的效果越明显。