批量删除資料方法

在一個Oracle資料庫運作過程中，有時候會遇到要批量删除資料的情況，如一個儲存曆史資料的表中有大量的資料已經不需要保留，要将這部分資料删除。通常采用的方法如下：

<b>1</b><b>、使用TRUNCATE</b><b>指令進行删除。 </b>

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

<b>2</b><b>、直接進行DELETE</b><b>操作。 </b>

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

是以這種方法也不理想。

<b>3</b><b>、采用删除分區的方式。</b>

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

下面介紹另外三種方法：

<b>方法一：</b>

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

下面是我的删除過程，我的資料表可以通過主鍵删除，測試過Delete和For all兩種方法，for all在這裡并沒有帶來性能提高，是以仍然選擇了批量直接删除。

首先建立一下過程，使用自制事務進行處理：(什麼事自治事物，這裡不過多闡述)

create or replace procedure delBigTab

(

p_TableName       in   

varchar2,

p_Condition       in   

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 &lt;= :rn'

USING p_Count;

if SQL%NOTFOUND then

exit;

else

n_delete:=n_delete + SQL%ROWCOUNT;

end if;

commit;

end loop;

DBMS_OUTPUT.PUT_LINE('Finished!');

DBMS_OUTPUT.PUT_LINE('Totally

'||to_char(n_delete)||' records deleted!');

end;

/

以下是删除過程及時間:

SQL&gt; create or replace procedure

delBigTab

2  (

3    p_TableName       in   

4    p_Condition       in   

5    p_Count        in   

6  )

7  as

8   pragma autonomous_transaction;

9   n_delete number:=0;

 10 

 11  

 12    

 13      

'delete from '||p_TableName||' where '||p_Condition||' and rownum &lt;=

:rn'

 14    

 15    

 16       

 17    

 18             

 19    

 20    

 21   end

loop;

 22  

 23  

 24  

DBMS_OUTPUT.PUT_LINE('Totally '||to_char(n_delete)||' records

deleted!');

 25  end;

 26  /

Procedure created.

SQL&gt; set timing on

SQL&gt; select min(NUMDLFLOGGUID) from

HS_DLF_DOWNLOG_HISTORY;

MIN(NUMDLFLOGGUID)

------------------

11000000

Elapsed: 00:00:00.23

SQL&gt; exec

delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID &lt; 11100000','10000');

PL/SQL procedure successfully completed.

Elapsed: 00:00:18.54

SQL&gt; select min(NUMDLFLOGGUID) from HS_DLF_DOWNLOG_HISTORY;

11100000

Elapsed: 00:00:00.18

SQL&gt; set serveroutput on

delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID &lt; 11200000','10000');

Finished!

Totally 96936 records deleted!

Elapsed: 00:00:18.61

10萬記錄大約19s

delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID &lt; 11300000','10000');

Totally 100000 records deleted!

Elapsed: 00:00:18.62

delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID &lt; 11400000','10000');

Elapsed: 00:00:18.85

SQL&gt;

delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID &lt; 13000000','10000');

Totally 1000000 records deleted!

Elapsed: 00:03:13.87

100萬記錄大約3分鐘

delBigTab('HS_DLF_DOWNLOG_HISTORY','NUMDLFLOGGUID &lt; 20000000','10000');

Totally 6999977 records deleted!

Elapsed: 00:27:24.69

700萬大約27分鐘

以上過程僅供參考.

<b>方法二：</b>

通過一段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 &lt; to_date('2000/01/01','yyyy/mm/dd') AND rownum = 1;

7 DELETE FROM t_table WHERE c_date &lt;

to_date('2000/01/01','yyyy/mm/dd') AND rownum &lt; 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，解決了這個問題，進而達到安全快速大量删除資料的目的。

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 &lt; 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 &lt;

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的各個版本。

<b>方法三：</b>

使用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&gt;='2006-03-10'

2.然後直接Truncate table

Table1。無論何種恢複模式都不會進行日志記錄

Truncate table Table1

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

exec sp_rename 'Table2','Table1'