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


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

首先创建一下过程，使用自制事务进行处理：
  1. 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;  
以下是删除过程及时间:
  1. SQL> create or replace procedure delBigTab  
  2.   2  (  
  3.   3    p_TableName       in    varchar2,  
  4.   4    p_Condition       in    varchar2,  
  5.   5    p_Count        in    varchar2  
  6.   6  )  
  7.   7  as  
  8.   8   pragma autonomous_transaction;  
  9.   9   n_delete number:=0;  
  10.  10  begin  
  11.  11   while 1=1 loop  
  12.  12     EXECUTE IMMEDIATE  
  13.  13       'delete from '||p_TableName||' where '||p_Condition||' and rownum <= :rn'  
  14.  14     USING p_Count;  
  15.  15     if SQL%NOTFOUND then  
  16.  16        exit;  
  17.  17     else  
  18.  18              n_delete:=n_delete + SQL%ROWCOUNT;  
  19.  19     end if;  
  20.  20     commit;  
  21.  21   end loop;  
  22.  22   commit;  
  23.  23   DBMS_OUTPUT.PUT_LINE('Finished!');  
  24.  24   DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records deleted!');  
  25.  25  end;  
  26.  26  /  
  27.   
  28. Procedure created.  
  29.   
  30. SQL> set timing on  
  31. SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;  
  32.   
  33. MIN(NUMDLFLOGGUID)  
  34. ------------------   
  35.           11000000  
  36.   
  37. Elapsed: 00:00:00.23  
  38. SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11100000','10000');  
  39.   
  40. PL/SQL procedure successfully completed.  
  41.   
  42. Elapsed: 00:00:18.54  
  43. SQL> select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;  
  44.   
  45. MIN(NUMDLFLOGGUID)  
  46. ------------------   
  47.           11100000  
  48.   
  49. Elapsed: 00:00:00.18  
  50. SQL> set serveroutput on  
  51. SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11200000','10000');  
  52. Finished!  
  53. Totally 96936 records deleted!  
  54.   
  55. PL/SQL procedure successfully completed.  
  56.   
  57. Elapsed: 00:00:18.61  
  58. 10万记录大约19s  
  59.   
  60. SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11300000','10000');  
  61. Finished!  
  62. Totally 100000 records deleted!  
  63.   
  64. PL/SQL procedure successfully completed.  
  65.   
  66. Elapsed: 00:00:18.62  
  67. SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 11400000','10000');  
  68. Finished!  
  69. Totally 100000 records deleted!  
  70.   
  71. PL/SQL procedure successfully completed.  
  72.   
  73. Elapsed: 00:00:18.85  
  74. SQL>  
  75. SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 13000000','10000');  
  76. Finished!  
  77. Totally 1000000 records deleted!  
  78.   
  79. PL/SQL procedure successfully completed.  
  80.   
  81. Elapsed: 00:03:13.87  
100万记录大约3分钟
  1. SQL> exec delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID < 20000000','10000');  
  2.    
  3. Finished!  
  4. Totally 6999977 records deleted!  
  5.   
  6. PL/SQL procedure successfully completed.  
  7.   
  8. Elapsed: 00:27:24.69  
700万大约27分钟
以上过程仅供参考.

相关内容