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

« Control SCN of Undo Segments | Blog首页 | Jonathan Lewis: Can we have a sensible debate ? »

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

作者：eygle |【转载时请以超链接形式标明文章出处和作者信息及本声明】
链接：http://www.eygle.com/archives/2005/04/oracleoeouaeeae.html

站内相关文章|Related Articles

10g临时表空间组导致递归SQL高度解析案例

Oracle的SQL可以有多长?

如何对时间进行简单加减运算

并行查询并行度Degree与instances 设置

关于PARALLEL_MAX_SERVERS参数的设置

批量删除海量数据通常都是很复杂及缓慢的，方法也很多，但是通常的概念是:分批删除，逐次提交。
下面是我的删除过程，我的数据表可以通过主键删除，测试过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分钟

以上过程仅供参考.

历史上的今天...
>> 2009-04-21文章:

沧海桑田 - 记纵贯线 2009 北京演唱会

>> 2008-04-21文章:

Resin、Apache、PHP与404错误

美丽的京剧美丽的晋剧

>> 2007-04-21文章:

在Oracle University大学演讲

悼念我的第一次淘宝购物经历

>> 2006-04-21文章:

IT168约以100万美元收购ITPUB

李白手书真迹-上阳台

By eygle on 2005-04-21 14:15 | Comments (16) | Posted to SQL.PLSQL | Edit |

相关文章	随机文章
10g临时表空间组导致递归SQL高度解析案例 Oracle的SQL可以有多长? 如何对时间进行简单加减运算并行查询并行度Degree与instances 设置关于PARALLEL_MAX_SERVERS参数的设置	如何才能防止拉网页? 不是春光胜似春光-北京植物园记行《循序渐进Oracle》一书出版《循序渐进Oracle》今日付印《循序渐进Oracle》第一章连载-之八

搜索本站:

留言 (16)

根据上面的描述做了一下test，有几个不明白的地方
1 主键换成此字段上的索引，两者的性能有何差异
2 上面的删除基于的环境是怎样的？这个性能跟具体的环境还有关系，我这里实验用的database，没有你这样的效果

Posted by: cedym at April 21, 2005 10:59 PM

我这里只是给出一种方法，具体环境因人而异，所以没有给出。
如果在你的环境里，你找得到更好的方法，那是最好的，你需要思考的是，如果不这样，还可以怎样？

Posted by: eygle at April 22, 2005 9:09 AM

有问题也。不能重复删除该表的记录.当我删除了一个表的全部记录后,然后向该表插入一些记录,再次删除却不能删除该表的记录.

Posted by: fans at May 7, 2005 4:40 PM

肯定可以的，你看看程序逻辑就知道了。
注意要根据你的表中数据定义你的删除条件。

Posted by: eygle at May 7, 2005 4:52 PM

commit;
end loop;
commit;
loop后面的这个commit是提交什么？

Posted by: copper at June 1, 2005 10:59 AM

删除大量数据后,表空间使用率仍然很高!
请问如何回收删除数据后的表空间?
用exp 导出imp导入?

Posted by: y98 at June 8, 2005 10:18 AM

DELETE并不能降低HWM，你可以exp,然后truncate table,然后imp，就可以释放出空间。

还有很多方法。

Posted by: eygle at June 8, 2005 11:19 AM

是啊，后面的那个commit是何意思？

Posted by: gnLeaf at September 15, 2005 4:44 PM

知道了。
AT程序必须以commit 或rollback结尾,否则会产生Oracle错误ORA-06519: active autonomous transaction detected and rolled back

Posted by: gnLeaf at September 15, 2005 5:39 PM

老大，请教一下：
那个p_Count就是你每次删除的条数的选择依照什么标准，还是经验值，或者依据哪些需要参考的地方，我试过我一个表的30万数据，以每次10万提交则需要200s，如果以1万需要更久的时间，

Posted by: xirour at September 20, 2005 9:52 AM

批量的选择跟你的环境有关系。

如果数据库比较繁忙，选择小一点的批量好一点，如果比较空闲，选择10W也无妨。

我给出的例子只是为了给大家一个通常的时间概念。
另，我的数据库较为繁忙，一般采用10w记录的批量是不错的。

实际上很多表会达到数亿的记录，这时候可能需要终和考虑其他手段，比如for all.

Posted by: eygle at September 20, 2005 10:14 AM

3ks

Posted by: xirour at September 20, 2005 5:06 PM

3ks

Posted by: xirour at September 20, 2005 5:07 PM

如果在condition里面要包含字符串，改怎么处理'呢？

Posted by: brett at March 29, 2007 3:55 PM

用两个单引号来处理:
SQL> exec delBigTab('EYGLE','username=''EYGLE''',2);
username='EYGLE'
username='EYGLE'
Finished!
Totally 1 records deleted!

PL/SQL procedure successfully completed.

Posted by: eygle at March 29, 2007 5:15 PM

如果可以停业务，而且删除的数据远远大于保留的数据。我觉得如下方法执行的速度会更快些，而且产生的redo log也比较小。
1)create table temp as select * from where ．．．
2)truncate table
3)insert into select * from temp
4)drop table temp

Posted by: muyizi2001 at March 17, 2009 4:30 PM

www.eygle.com

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

留言 (16)

发表留言: