« Control SCN of Undo Segments | Blog首页 | Jonathan Lewis: Can we have a sensible debate ? »
Oracle中大批量删除数据的方法
作者:eygle | 【转载请注出处】|【云和恩墨 领先的zData数据库一体机 | zCloud PaaS云管平台 | SQM SQL审核平台 | ZDBM 数据库备份一体机】
链接:https://www.eygle.com/archives/2005/04/oracleoeouaeeae.html
批量删除海量数据通常都是很复杂及缓慢的,方法也很多,但是通常的概念是:分批删除,逐次提交。链接:https://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分钟
以上过程仅供参考.
历史上的今天...
>> 2020-04-21文章:
>> 2012-04-21文章:
>> 2011-04-21文章:
>> 2010-04-21文章:
>> 2009-04-21文章:
>> 2008-04-21文章:
>> 2007-04-21文章:
>> 2006-04-21文章:
By eygle on 2005-04-21 14:15 | Comments (17) | SQL.PLSQL | 265 |
根据上面的描述做了一下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.
3ks
3ks
如果在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
太感谢了!太感谢了!