eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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

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分钟
以上过程仅供参考.


历史上的今天...
    >> 2012-04-21文章:
    >> 2011-04-21文章:
    >> 2010-04-21文章:
    >> 2009-04-21文章:
    >> 2008-04-21文章:
           Resin、Apache、PHP与404错误
           美丽的京剧 美丽的晋剧
    >> 2007-04-21文章:
           在Oracle University大学演讲
    >> 2006-04-21文章:
           李白手书真迹-上阳台

无觅

By eygle on 2005-04-21 14:15 | Comments (17) | SQL.PLSQL | 265 |

17 Comments

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

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

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

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

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

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

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

还有很多方法。

是啊,后面的那个commit是何意思?

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

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

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

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

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

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

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

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

PL/SQL procedure successfully completed.

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

太感谢了!太感谢了!


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com