Oracle 海量數據批量刪除方案：分批提交與自治事務實戰

核心挑戰

直接 DELETE FROM big_table WHERE condition 會導致：

• 巨大的 UNDO 產生，撞滿 UNDO 表空間
• 長事務鎖定表，引發鎖等待甚至死鎖
• 高水位線不下降，後續全表掃描性能劣化

最佳實踐：分批循環刪除 + 自治事務

核心思想：小批量、高頻次、獨立提交，將大事務拆解為眾多微事務。

通用存儲過程實現

CREATE OR REPLACE PROCEDURE del_big_table (
  p_table_name  IN VARCHAR2,  -- 目標表名
  p_condition   IN VARCHAR2,  -- 刪除條件（不含 WHERE）
  p_batch_size  IN NUMBER     -- 每批次行數
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;  -- 關鍵：每次循環獨立提交
  v_deleted_cnt NUMBER := 0;
  v_total_cnt   NUMBER := 0;
BEGIN
  LOOP
    EXECUTE IMMEDIATE
      'DELETE FROM ' || p_table_name || 
      ' WHERE ' || p_condition || ' AND ROWNUM <= :bn'
    USING p_batch_size;

    v_deleted_cnt := SQL%ROWCOUNT;
    v_total_cnt   := v_total_cnt + v_deleted_cnt;

    COMMIT;  -- 釋放鎖、UNDO 空間

    EXIT WHEN v_deleted_cnt = 0;  -- 無數據可刪則退出
    
    -- 可選：短暫休眠降低系統壓力
    -- DBMS_LOCK.SLEEP(0.1);
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('完成！共刪除 ' || v_total_cnt || ' 條記錄。');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('錯誤: ' || SQLERRM);
    RAISE;
END;
/

調用示例

-- 刪除 30 天前的日誌，每次 5000 行
BEGIN
  del_big_table('SYS_LOG', 'CREATE_TIME < SYSDATE - 30', 5000);
END;
/
-- 或直接在 SQL*Plus 執行
EXEC del_big_table('ORDERS_HIST', 'STATUS = ''CANCELLED''', 10000);

方案對比與選型

進階優化建議

1. 索引策略：刪除前標記索引 UNUSABLE，結束後 REBUILD，避免索引維護開銷。
2. 並行刪除：大表可啟用 PARALLEL (DEGREE 4)，配合分批邏輯。
3. 監控指標：實時查詢 V$SESSION_LONGOPS、V$UNDOSTAT 確保 UNDO 穩定。
4. 高水位線：刪除完成後執行 ALTER TABLE ... ENABLE ROW MOVEMENT; ALTER TABLE ... SHRINK SPACE; 釋放空間。

FORALL 方案補充

雖原文測試 FORALL 無優勢，但對於「已知主鍵 ID 列表」的場景，FORALL 批量綁定仍有價值：

TYPE id_tab_t IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
v_ids id_tab_t;
-- 填充 v_ids ...
FORALL i IN INDICES OF v_ids
  DELETE FROM target_table WHERE id = v_ids(i);

總結： 分批刪除配合自治事務是通用性最強的「無停機瘦身術」。根據表大小、業務窗口、分區架構靈活選型，才是資深 DBA 的必修功課。