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

来源：互联网发布：学霸精软件下载编辑：程序博客网时间：2024/06/05 09:07

批量删除海量数据通常都是很复杂及缓慢的，方法也很多，但是通常的概念是:分批删除，逐次提交。
下面是我的删除过程，我的数据表可以通过主键删除，测试过Delete和For all两种方法，for all在这里并没有带来性能提高，所以仍然选择了批量直接删除。

首先创建一下过程，使用自制事务进行处理：

create or replace procedure delBigTab(p_TableName       in    varchar2,p_Condition       in    varchar2,p_Count        in    varchar2)aspragma autonomous_transaction;n_delete number:=0;beginwhile 1=1 loopEXECUTE IMMEDIATE'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'USING p_Count;if SQL%NOTFOUND thenexit;elsen_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 onSQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;MIN(NUMDLFLOGGUID)------------------          11000000Elapsed: 00:00:00.23SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000');PL/SQL procedure successfully completed.Elapsed: 00:00:18.54SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;MIN(NUMDLFLOGGUID)------------------          11100000Elapsed: 00:00:00.18SQL> set serveroutput onSQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000');Finished!Totally 96936 records deleted!PL/SQL procedure successfully completed.Elapsed: 00:00:18.6110万记录大约19sSQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000');Finished!Totally 100000 records deleted!PL/SQL procedure successfully completed.Elapsed: 00:00:18.62SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000');Finished!Totally 100000 records deleted!PL/SQL procedure successfully completed.Elapsed: 00:00:18.85SQL> SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000');Finished!Totally 1000000 records deleted!PL/SQL procedure successfully completed.Elapsed: 00:03:13.87100万记录大约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.69700万大约27分钟

0 0