Oracle中大批量删除数据的方法

批量删除海量数据通常都是很复杂及缓慢的，方法也很多，但是通常的概念是:分批删除，逐次提交。
下面是我的删除过程，我的数据表可以通过主键删除，测试过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分钟
以上过程仅供参考.如何更改Oracle中schema或user的名字sqlplus 执行脚本文件时如何传参数相关资讯 Oracle数据库 Oracle入门教程 oracle数据库教程

Oracle数据库全球化（03月01日）
Oracle数据库日期过滤方法性能比较（02/02/2015 13:20:26）
Oracle数据库安装中端口被占用问题（10/29/2014 07:42:24）

在CentOS 6.6上搭建C++运行环境并（10/10/2015 19:44:40）
Oracle数据库无法使用localhost和（11/14/2014 16:39:10）
使用SQLT来构建Oracle测试用例（08/28/2014 06:17:41）

本文评论查看全部评论（0）