Oracle 大量資料刪除效能優化：分批提交與替代方案實戰

問題背景

使用 DELETE 刪除百萬筆資料時極慢甚至當機，主因是資料庫需產生大量重做日誌以支援回滾，導致 Undo 表空間暴增、鎖爭用劇烈、日誌切換頻繁。

網友方案一：游標迴圈分批刪除

CREATE OR REPLACE PROCEDURE delete_table IS
    i NUMBER(10) := 0;
BEGIN
    FOR x IN (SELECT * FROM emp WHERE deptno LIKE 'A%') LOOP
        DELETE FROM emp WHERE emp.id = x.id;
        i := i + 1;
        IF i > 1000 THEN
            COMMIT;
            i := 0;
        END IF;
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE);
        ROLLBACK;
END delete_table;

優點：釋放 Undo 空間、減少鎖持有時間。缺點：逐行處理效能低、上下文切換開銷大。

網友方案二：定量迴圈刪除

DECLARE
    CNT NUMBER(10) := 0;
    I   NUMBER(10);
BEGIN
    SELECT COUNT(*) INTO CNT FROM ep_arrearage_bak 
        WHERE TO_CHAR(DF_DATE, 'MM') = '01';
    FOR I IN 1..TRUNC(CNT/500)+1 LOOP
        DELETE FROM ep_arrearage_bak 
            WHERE TO_CHAR(DF_DATE, 'MM') = '01' AND ROWNUM <= 500;
        COMMIT;
    END LOOP;
END;

每次刪 500 筆並提交，優於方案一，但仍有全表掃描重複開銷。

專家建議的四大策略

1. 保留法：若刪除大部分資料，將保留資料插入臨時表 → TRUNCATE TABLE → 再插回。TRUNCATE 不產生日誌、瞬間完成、釋放 High Water Mark。
2. 分批提交：如方案二，控制每批筆數（建議 1000-5000）。
3. 大型 Undo 區段：建立專用大 Undo Tablespace 指定交易使用。
4. 非歸檔模式：確認無需恢復時，切換 NOARCHIVELOG 模式刪除，完再切回並備份。

專家提供的通用分批刪除存儲過程

CREATE OR REPLACE PROCEDURE deleteTab (
    p_TableName  IN VARCHAR2,              -- 目標表名
    p_Condition  IN VARCHAR2 DEFAULT '1=1', -- 刪除條件
    p_Count      IN VARCHAR2 DEFAULT '10000'-- 每批筆數
) 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! Totally ' || n_delete || ' records deleted!');
END;
/

使用範例

-- 建立測試表
CREATE TABLE test AS SELECT * FROM dba_objects;
-- 重複插入擴充資料量
INSERT INTO test SELECT * FROM dba_objects;  -- 執行多次
COMMIT;

-- 執行刪除：刪除 object_id > 0 的資料，每 10000 筆提交
EXEC deleteTab('TEST', 'object_id > 0', '10000');

關鍵技術點解析

• PRAGMA AUTONOMOUS_TRANSACTION：使過程在獨立事務中執行，可在主事務未提交時獨立 COMMIT
• EXECUTE IMMEDIATE ... USING：動態 SQL 綁定變數，避免 SQL 注入、提升解析重用
• ROWNUM <= :rn：限制每次刪除筆數
• SQL%ROWCOUNT / SQL%NOTFOUND：判斷影響筆數與是否結束

實戰建議總結

核心原則：避免單一大事務，透過分批提交控制 Undo 產生、減少鎖爭用、平滑日誌產生，實現大量刪除的平穩執行。