Oracle大批量刪除數(shù)據(jù)方法
2024-08-29 13:43:47
供稿：網(wǎng)友
批量刪除海量數(shù)據(jù)通常都是很復(fù)雜及緩慢的，方法也很多，但是通常的概念是:分批刪除，逐次提交。下面是刪除過程，數(shù)據(jù)表可以通過主鍵刪除，測試過Delete和For all兩種方法，for all在這里并沒有帶來性能提高，所以仍然選擇了批量直接刪除。 首先創(chuàng)建一下過程，使用自制事務(wù)進(jìn)行處理：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)
------------------
11000000Elapsed: 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)
------------------
11100000Elapsed: 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萬記錄大約19sSQL> 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.87100萬記錄大約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分鐘以上過程僅供參考.
