当前页面位置: » 丰搜网 » 文档中心 » 详细内容
高手的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
