模板
教程
环境
性能
功能
管理
Oracle大批量删除数据方法
批量删除海量数据通常都是很复杂及缓慢的，方法也很多，但是通常的概念是:分批删除，逐次提交。
下面是删除过程，数据表可以通过主键删除，测试过Delete和For all两种方法，for all在这里并没有带来性能提高，所以仍然选择了批量直接删除。 首先创建一下过程，使用自制事务进行处理： create or replace procedure delBigTab 以下是删除过程及时间: Procedure created. SQL> set timing on MIN(NUMDLFLOGGUID) Elapsed: 00:00:00.23 PL/SQL procedure suclearcase/" target="_blank" >ccessfully completed. Elapsed: 00:00:18.54 MIN(NUMDLFLOGGUID) Elapsed: 00:00:00.18 PL/SQL procedure successfully completed. Elapsed: 00:00:18.61 SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000'); PL/SQL procedure successfully completed. Elapsed: 00:00:18.62 PL/SQL procedure successfully completed. Elapsed: 00:00:18.85 PL/SQL procedure successfully completed. Elapsed: 00:03:13.87 100万记录大约3分钟 Finished! PL/SQL procedure successfully completed. Elapsed: 00:27:24.69 以上过程仅供参考.
(
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 /
SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;
------------------
11000000
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000');
SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;
------------------
11100000
SQL> set serveroutput on
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000');
Finished!
Totally 96936 records deleted!
10万记录大约19s
Finished!
Totally 100000 records deleted!
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000');
Finished!
Totally 100000 records deleted!
SQL>
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000');
Finished!
Totally 1000000 records deleted!
SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 20000000','10000');
Totally 6999977 records deleted!
700万大约27分钟
