溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章將為大家詳細講解有關數據庫中如何批量刪除數據，小編覺得挺實用的，因此分享給大家做個參考，希望大家閱讀完這篇文章后可以有所收獲。
在一個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'
關于“數據庫中如何批量刪除數據”這篇文章就分享到這里了，希望以上內容可以對大家有一定的幫助，使各位可以學到更多知識，如果覺得文章不錯，請把它分享出去讓更多的人看到。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
