數(shù)據(jù)庫(kù)中如何批量刪除數(shù)據(jù)

這篇文章將為大家詳細(xì)講解有關(guān)數(shù)據(jù)庫(kù)中如何批量刪除數(shù)據(jù)，小編覺(jué)得挺實(shí)用的，因此分享給大家做個(gè)參考，希望大家閱讀完這篇文章后可以有所收獲。

在一個(gè)Oracle數(shù)據(jù)庫(kù)運(yùn)行過(guò)程中，有時(shí)候會(huì)遇到要批量刪除數(shù)據(jù)的情況，如一個(gè)保存歷史數(shù)據(jù)的表中有大量的數(shù)據(jù)已經(jīng)不需要保留，要將這部分?jǐn)?shù)據(jù)刪除。通常采用的方法如下：

1、使用TRUNCATE命令進(jìn)行刪除。

如果是整個(gè)表的數(shù)據(jù)都要?jiǎng)h除的話(huà)，使用TRUNCATE TABLE命令是理想的選擇。它刪除了表中的所有數(shù)據(jù)，并且因?yàn)椴粚?xiě)REDO LOG FILE，所以速度很快。刪除的同時(shí)，表的索引和約束條件仍然存在。這種方法適用于ORACLE的各個(gè)版本。但是當(dāng)要?jiǎng)h除的數(shù)據(jù)只是表中的一部分時(shí)，這種方法便行不通了。

2、直接進(jìn)行DELETE操作。

直接使用DELETE命令進(jìn)行刪除，如果刪除的數(shù)據(jù)量較大時(shí)，可能導(dǎo)致回滾段出錯(cuò)。這是因?yàn)樵趧h除數(shù)據(jù)的過(guò)程中，不斷擴(kuò)展回滾段，直到回滾段的最大范圍數(shù)或回滾段所在表空間空閑空間用完而出錯(cuò)。解決這個(gè)問(wèn)題可以通過(guò)給刪除數(shù)據(jù)的事務(wù)指定一個(gè)足夠大的回滾段或者將回滾段所在表空間的AUTOEXTEND選項(xiàng)打開(kāi)，同時(shí)將回滾段的MAXEXTENTS改大或設(shè)為UNLIMITED。不過(guò)這樣仍存在一個(gè)隱患，如果刪除的數(shù)據(jù)量大，同時(shí)數(shù)據(jù)庫(kù)工作于歸檔模式下時(shí)，有可能導(dǎo)致日志切換頻繁，所有日志文件都處于需要?dú)w檔的狀況，而歸檔進(jìn)程來(lái)不及歸檔日志文件的情況出現(xiàn)，這時(shí)數(shù)據(jù)庫(kù)將被掛起，直到有可用的日志文件后才恢復(fù)正常。

所以這種方法也不理想。

3、采用刪除分區(qū)的方式。

比如若是按照時(shí)間做的分區(qū)表，drop partition刪除分區(qū)的操作可能是效率最快的、最簡(jiǎn)單的。但是使用分區(qū)表的情況也不是很多。

下面介紹另外三種方法：

方法一：

批量刪除海量數(shù)據(jù)通常都是很復(fù)雜及緩慢的，方法也很多，但是通常的概念是:分批刪除，逐次提交。

下面是我的刪除過(guò)程，我的數(shù)據(jù)表可以通過(guò)主鍵刪除，測(cè)試過(guò)Delete和For all兩種方法，for all在這里并沒(méi)有帶來(lái)性能提高，所以仍然選擇了批量直接刪除。

首先創(chuàng)建一下過(guò)程，使用自制事務(wù)進(jìn)行處理：(什么事自治事物，這里不過(guò)多闡述)

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;

/

以下是刪除過(guò)程及時(shí)間:

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萬(wàn)記錄大約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萬(wàn)記錄大約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萬(wàn)大約27分鐘

以上過(guò)程僅供參考.

方法二：

通過(guò)一段PL/SQL程序循環(huán)分段刪除數(shù)據(jù)，逐步提交事務(wù)，達(dá)到縮小事務(wù)規(guī)模，安全刪除數(shù)據(jù)的目的。 

例如有一個(gè)數(shù)據(jù)表t_table，我們將對(duì)其中字段c_date滿(mǎn)足小于2001年1月1日的記錄進(jìn)行刪除，可以采用以下的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行聲明了一個(gè)臨時(shí)變量。第4到第13行定義了一個(gè)循環(huán)，在這個(gè)循環(huán)中第6行不斷檢查表中是否還有滿(mǎn)足條件的記錄，如果有，第7行程序便執(zhí)行刪除操作，每次刪除100記錄，同時(shí)提交事務(wù)。當(dāng)表中已無(wú)滿(mǎn)足條件的記錄時(shí)，便引起NO_DATA_FOUND的異常，從而退出循環(huán)。通過(guò)分批刪除，逐步提交，縮小了事務(wù)的規(guī)模，從而達(dá)到避免出現(xiàn)回滾段錯(cuò)誤的目的。然而這種方法依然存在因日志切換頻繁，而歸檔進(jìn)程來(lái)不及歸檔日志文件而導(dǎo)致數(shù)據(jù)庫(kù)掛起的可能性。下面的程序通過(guò)ORACLE所提供的dbms_lock包中的過(guò)程sleep，解決了這個(gè)問(wèn)題，從而達(dá)到安全快速大量刪除數(shù)據(jù)的目的。

1 DECLARE

2 V_LOGNUM NUMBER; -- 數(shù)據(jù)庫(kù)中擁有的日志文件數(shù)

3 V_NEEDARC NUMBER; -- 需要?dú)w檔的日志文件數(shù)

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行聲明了兩個(gè)變量v_lognum和v_needarc來(lái)保存數(shù)據(jù)庫(kù)中日志文件的數(shù)量和當(dāng)前需要?dú)w檔的日志文件數(shù)量。

第5行獲取了數(shù)據(jù)庫(kù)中日志文件的數(shù)量。

第6行到第21行開(kāi)始了刪除數(shù)據(jù)的循環(huán)，第7行到第14行是一個(gè)子循環(huán)，不斷檢測(cè)當(dāng)前需要?dú)w檔的日志文件的數(shù)量v_needarc是否小于數(shù)據(jù)庫(kù)的日志文件總數(shù)v_lognum減去1，如果滿(mǎn)足條件，則退出子循環(huán)，開(kāi)始刪除數(shù)據(jù)。否則的話(huà)便調(diào)用dbms_lock.sleep()過(guò)程，使程序休眠60秒，然后繼續(xù)子循環(huán)，檢測(cè)需歸檔的日志文件數(shù)量。

第17到19行，檢查刪除數(shù)據(jù)的結(jié)果，如果已無(wú)數(shù)據(jù)，則退出，程序結(jié)束。

這個(gè)程序，通過(guò)利用dbms_output.sleep()過(guò)程，在刪除過(guò)程中當(dāng)需要?dú)w檔的日志文件達(dá)到認(rèn)定的限制時(shí)，使刪除過(guò)程暫時(shí)停止，等待ARCH進(jìn)程將日志文件歸檔后再繼續(xù)進(jìn)行，從而達(dá)到避免歸檔日志文件來(lái)不及歸檔，導(dǎo)致數(shù)據(jù)庫(kù)掛起的問(wèn)題。

此方法適用于oracle的各個(gè)版本。

方法三：

使用NOLOGGING選項(xiàng)重新建表。 

在ORACLE 8以后的版本中，CREATE TABLE命令提供了NOLOGGING的選項(xiàng)，在建表時(shí)不用寫(xiě)日志文件。

這樣當(dāng)我們?cè)趧h除大量的數(shù)據(jù)時(shí)可以將要保留的數(shù)據(jù)通過(guò)CREATE TABLE ... NOLOGGING ... AS SELECT * FROM...的方法將要保留的數(shù)據(jù)備份到另一個(gè)表中，將原來(lái)的表刪除，然后再 ALTER TABLE RENAME TO 命令將備份的表改為原來(lái)表的名字。

這個(gè)方法由于不寫(xiě)日志文件，所以速度很快，但是原來(lái)的表所擁有的索引和約束都將不存在，需重新建立。另外這個(gè)方法只適用于ORACLE 8以后的版本。

用turncate table table1

因?yàn)?span lang="EN-US">truncate 是DDL操作，不產(chǎn)生rollback，不寫(xiě)日志速度快一些，然后如果有自增的話(huà)，恢復(fù)到1開(kāi)始。

而delete會(huì)產(chǎn)生rollback，如果刪除大數(shù)據(jù)量的表速度會(huì)很慢，同時(shí)會(huì)占用很多的rollback segments，同時(shí)還要記錄下G級(jí)別的日志。

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

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

2.然后直接Truncate table Table1。無(wú)論何種恢復(fù)模式都不會(huì)進(jìn)行日志記錄

Truncate table Table1

3.最后對(duì)Table2進(jìn)行改名為Table1

exec sp_rename 'Table2','Table1'

關(guān)于“數(shù)據(jù)庫(kù)中如何批量刪除數(shù)據(jù)”這篇文章就分享到這里了，希望以上內(nèi)容可以對(duì)大家有一定的幫助，使各位可以學(xué)到更多知識(shí)，如果覺(jué)得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。