批次刪除資料方法

在一個Oracle資料庫執行過程中，有時候會遇到要批次刪除資料的情況，如一個儲存歷史資料的表中有大量的資料已經不需要保留，要將這部分資料刪除。通常採用的方法如下：

1、使用TRUNCATE命令進行刪除。

如果是整個表的資料都要刪除的話，使用TRUNCATE TABLE命令是理想的選擇。它刪除了表中的所有資料，並且因為不寫REDO LOG FILE，所以速度很快。刪除的同時，表的索引和約束條件仍然存在。這種方法適用於ORACLE的各個版本。但是當要刪除的資料只是表中的一部分時，這種方法便行不通了。

2、直接進行DELETE操作。

直接使用DELETE命令進行刪除，如果刪除的資料量較大時，可能導致回滾段出錯。這是因為在刪除資料的過程中，不斷擴充套件回滾段，直到回滾段的最大範圍數或回滾段所在表空間空閒空間用完而出錯。解決這個問題可以透過給刪除資料的事務指定一個足夠大的回滾段或者將回滾段所在表空間的AUTOEXTEND選項開啟，同時將回滾段的MAXEXTENTS改大或設為UNLIMITED。不過這樣仍存在一個隱患，如果刪除的資料量大，同時資料庫工作于歸檔模式下時，有可能導致日誌切換頻繁，所有日誌檔案都處於需要歸檔的狀況，而歸檔程式來不及歸檔日誌檔案的情況出現，這時資料庫將被掛起，直到有可用的日誌檔案後才恢復正常。

所以這種方法也不理想。

3、採用刪除分割槽的方式。

比如若是按照時間做的分割槽表，drop partition刪除分割槽的操作可能是效率最快的、最簡單的。但是使用分割槽表的情況也不是很多。

下面介紹另外三種方法：

方法一：

批次刪除海量資料通常都是很複雜及緩慢的，方法也很多，但是通常的概念是:分批刪除，逐次提交。

下面是我的刪除過程，我的資料表可以透過主鍵刪除，測試過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分鐘

以上過程僅供參考.

方法二：

透過一段PL/SQL程式迴圈分段刪除資料，逐步提交事務，達到縮小事務規模，安全刪除資料的目的。 

例如有一個資料表t_table，我們將對其中欄位c_date滿足小於2001年1月1日的記錄進行刪除，可以採用以下的PL/SQL程式。

1 DECLARE

2 V_TEMP NUMBER;

3 BEGIN

4 LOOP

5 BEGIN

6 SELECT 1 INTO V_TEMP FROM t_table WHERE c_date < to_date('2000/01/01','yyyy/mm/dd') AND rownum = 1;

7 DELETE FROM t_table WHERE c_date < to_date('2000/01/01','yyyy/mm/dd') AND rownum < 100;

8 COMMIT;

9 EXCEPTION 

10 WHEN NO_DATA_FOUND THEN

11 EXIT;

12 END;

13 END LOOP;

14 END;

程式的第1和第2行宣告瞭一個臨時變數。第4到第13行定義了一個迴圈，在這個迴圈中第6行不斷檢查表中是否還有滿足條件的記錄，如果有，第7行程式便執行刪除操作，每次刪除100記錄，同時提交事務。當表中已無滿足條件的記錄時，便引起NO_DATA_FOUND的異常，從而退出迴圈。透過分批刪除，逐步提交，縮小了事務的規模，從而達到避免出現回滾段錯誤的目的。然而這種方法依然存在因日誌切換頻繁，而歸檔程式來不及歸檔日誌檔案而導致資料庫掛起的可能性。下面的程式透過ORACLE所提供的dbms_lock包中的過程sleep，解決了這個問題，從而達到安全快速大量刪除資料的目的。

1 DECLARE

2 V_LOGNUM NUMBER; -- 資料庫中擁有的日誌檔案數

3 V_NEEDARC NUMBER; -- 需要歸檔的日誌檔案數

4 BEGIN

5 SELECT count(1) INTO V_LOGNUM FROM V$LOG;

6 LOOP

7 LOOP

8 SELECT count(1) INTO V_NEEDARC FROM V$ARCHIVE;

9 IF V_NEEDARC < V_LOGNUM - 1 THEN

10 EXIT;

11 ELSE

12 DBMS_LOCK.SLEEP(60);

13 END IF;

14 END LOOP;

15

16 DELETE FROM t_table WHERE c_date < to_date('2000/01/01','yyyy/mm/dd') AND rownum < 100;

17 IF SQL%ROWCOUNT = 0 THEN

18 EXIT;

19 END IF;

20 COMMIT;

21 END LOOP;

22 END;

程式中的第2和第3行宣告瞭兩個變數v_lognum和v_needarc來儲存資料庫中日誌檔案的數量和當前需要歸檔的日誌檔案數量。

第5行獲取了資料庫中日誌檔案的數量。

第6行到第21行開始了刪除資料的迴圈，第7行到第14行是一個子迴圈，不斷檢測當前需要歸檔的日誌檔案的數量v_needarc是否小於資料庫的日誌檔案總數v_lognum減去1，如果滿足條件，則退出子迴圈，開始刪除資料。否則的話便呼叫dbms_lock.sleep()過程，使程式休眠60秒，然後繼續子迴圈，檢測需歸檔的日誌檔案數量。

第17到19行，檢查刪除資料的結果，如果已無資料，則退出，程式結束。

這個程式，透過利用dbms_output.sleep()過程，在刪除過程中當需要歸檔的日誌檔案達到認定的限制時，使刪除過程暫時停止，等待ARCH程式將日誌檔案歸檔後再繼續進行，從而達到避免歸檔日誌檔案來不及歸檔，導致資料庫掛起的問題。

此方法適用於oracle的各個版本。

方法三：

使用NOLOGGING選項重新建表。 

在ORACLE 8以後的版本中，CREATE TABLE命令提供了NOLOGGING的選項，在建表時不用寫日誌檔案。

這樣當我們在刪除大量的資料時可以將要保留的資料透過CREATE TABLE ... NOLOGGING ... AS SELECT * FROM...的方法將要保留的資料備份到另一個表中，將原來的表刪除，然後再 ALTER TABLE RENAME TO 命令將備份的表改為原來表的名字。

這個方法由於不寫日誌檔案，所以速度很快，但是原來的表所擁有的索引和約束都將不存在，需重新建立。另外這個方法只適用於ORACLE 8以後的版本。

用turncate table table1

因為truncate 是DDL操作，不產生rollback，不寫日誌速度快一些，然後如果有自增的話，恢復到1開始。

而delete會產生rollback，如果刪除大資料量的錶速度會很慢，同時會佔用很多的rollback segments，同時還要記錄下G級別的日誌。

1.選出您所需要保留的記錄到新的表

Select * into Table2 From Table1 Where Time>='2006-03-10'

2.然後直接Truncate table Table1。無論何種恢復模式都不會進行日誌記錄

Truncate table Table1

3.最後對Table2進行改名為Table1

exec sp_rename 'Table2','Table1'