共計 5272 個字符，預計需要花費 14 分鐘才能閱讀完成。

這篇文章將為大家詳細講解有關數據庫中如何批量刪除數據，丸趣 TV 小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。

在一個 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

關于“數據庫中如何批量刪除數據”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。