Oracle大批量刪除數據方法_數據庫

Oracle大批量刪除數據方法

發表于：2007-07-13來源：作者：點擊數：標簽：

批量刪除海量數據通常都是很復雜及緩慢的，方法也很多，但是通常的概念是:分批刪除，逐次提交。下面是刪除過程，數據表可以通過主鍵刪除，測試過Delete和For all兩種方法，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 suclearcase/" target="_blank" >ccessfully 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!