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

成都創(chuàng)新互聯(lián)長(zhǎng)期為上1000+客戶提供的網(wǎng)站建設(shè)服務(wù)，團(tuán)隊(duì)從業(yè)經(jīng)驗(yàn)10年，關(guān)注不同地域、不同群體，并針對(duì)不同對(duì)象提供差異化的產(chǎn)品和服務(wù)；打造開放共贏平臺(tái)，與合作伙伴共同營(yíng)造健康的互聯(lián)網(wǎng)生態(tài)環(huán)境。為碭山企業(yè)提供專業(yè)的成都網(wǎng)站設(shè)計(jì)、做網(wǎng)站，碭山網(wǎng)站改版等技術(shù)服務(wù)。擁有10多年豐富建站經(jīng)驗(yàn)和眾多成功案例,為您定制開發(fā)。

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

所以這種方法也不理想。

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

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

下面介紹另外三種方法：

方法一：

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

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

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

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;

/

以下是刪除過程及時(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萬記錄大約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程序循環(huán)分段刪除數(shù)據(jù)，逐步提交事務(wù)，達(dá)到縮小事務(wù)規(guī)模，安全刪除數(shù)據(jù)的目的。 

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

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

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

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

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

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

方法三：

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

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

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

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

用turncate table table1

因?yàn)閠runcate是DDL操作，不產(chǎn)生rollback，不寫日志速度快一些，然后如果有自增的話，恢復(fù)到1開始。

而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。無論何種恢復(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í)，如果覺得文章不錯(cuò)，請(qǐng)把它分享出去讓更多的人看到。