ITPub博客

首页 > Linux操作系统 > Linux操作系统 > LARGE DELETE快速删除

LARGE DELETE快速删除

原创 Linux操作系统 作者:xmlct78 时间:2007-06-28 00:00:00 0 删除 编辑

看了ITPUB出的9I优化中《如何给Large Delete操作提速近千倍?》,今天做了个测试。

需求:删除表serviceinfosellername重复的记录,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_segdel_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%

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


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/69204/viewspace-45358/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 我爱我佳
请登录后发表评论 登录
全部评论

注册时间:2007-12-06

  • 博文量
    16
  • 访问量
    66772