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

查看本文来源