LARGE DELETE快速刪除

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

由於伺服器效能的限制，沒有再進一步測試，相信資料量越大，最佳化的效果越明顯。