WEB开发网
开发学院数据库Oracle Oracle大批量删除数据方法 阅读

Oracle大批量删除数据方法

 2007-05-07 12:08:07 来源:WEB开发网   
核心提示:批量删除海量数据通常都是很复杂及缓慢的,方法也很多,Oracle大批量删除数据方法,但是通常的概念是:分批删除,逐次提交,for all在这里并没有带来性能提高,所以仍然选择了批量直接删除,下面是删除过程,数据表可以通过主键删除

批量删除海量数据通常都是很复杂及缓慢的,方法也很多,但是通常的概念是:分批删除,逐次提交。

下面是删除过程,数据表可以通过主键删除,测试过Delete和For all两种方法,for all在这里并没有带来性能提高,所以仍然选择了批量直接删除。

首先创建一下过程,使用自制事务进行处理:

create or replace procedure delBigTab
(
p_TableName in varchar2,
p_Condition in varchar2,
p_Count in varchar2
)
as
pragma autonomous_transaction;
n_delete number:=0;
begin
while 1=1 loop
EXECUTE IMMEDIATE
'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'
USING p_Count;
if SQL%NOTFOUND then
exit;
else
n_delete:=n_delete + SQL%ROWCOUNT;
end if;
commit;
end loop;
commit;
DBMS_OUTPUT.PUT_LINE('Finished!');
DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');
end;

以下是删除过程及时间:

SQL> create or replace procedure delBigTab

2 (

3 p_TableName in varchar2,

4 p_Condition in varchar2,

5 p_Count in varchar2

6 )

7 as

8 pragma autonomous_transaction;

9 n_delete number:=0;

10 begin

11 while 1=1 loop

12 EXECUTE IMMEDIATE

13 'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'

14 USING p_Count;

15 if SQL%NOTFOUND then

16 exit;

17 else

18 n_delete:=n_delete + SQL%ROWCOUNT;

19 end if;

20 commit;

21 end loop;

22 commit;

23 DBMS_OUTPUT.PUT_LINE('Finished!');

24 DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');

25 end;

26 /

Procedure created.

SQL> set timing on

SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN(NUMDLFLOGGUID)

------------------

11000000

Elapsed: 00:00:00.23

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.54

SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

MIN(NUMDLFLOGGUID)

------------------

11100000

Elapsed: 00:00:00.18

SQL> set serveroutput on

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000');

Finished!

Totally 96936 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.61

10万记录大约19s

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000');

Finished!

Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.62

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000');

Finished!

Totally 100000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.85

SQL>

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000');

Finished!

Totally 1000000 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:03:13.87

100万记录大约3分钟

SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 20000000','10000');

Finished!

Totally 6999977 records deleted!

PL/SQL procedure successfully completed.

Elapsed: 00:27:24.69

700万大约27分钟

以上过程仅供参考.

Tags:Oracle 大批量 删除

编辑录入:爽爽 [复制链接] [打 印]
赞助商链接