« 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
批量删除海量数据通常都是很复杂及缓慢的，方法也很多，但是通常的概念是:分批删除，逐次提交。
下面是我的删除过程，我的数据表可以通过主键删除，测试过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中大批量删除数据的方法】来自
www.eygle.com |
CSDN技术网摘|
del.icio.us|
365Key
By eygle on 2005-04-21 14:15 |
Comments (15) |
Posted to
SQL.PLSQL | Edit |Pageviews:
网上相关主题:
根据上面的描述做了一下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
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
Posted by: xirour at September 20, 2005 5:06 PM
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
发表留言:
