Oracle 千萬級數據高效刪除策略：分批提交與分區表優化

場景痛點

Oracle 10g 單表 5000 萬條數據，需刪除 3000 萬歷史記錄。表上有「創建日期」索引和「Sequence ID」索引。直接執行 DELETE 會導致：

• 巨大的 Undo 日誌產生，可能撐爆 UNDO 表空間。
• 大量 Redo 日誌寫入，嚴重拖慢性能。
• 長時間鎖表，阻塞業務讀寫。
• High Water Mark (HWM) 不下降，釋放空間需額外操作。

核心優化方案

方案一：分批循環刪除（最通用）

DECLARE
  v_deleted NUMBER := 0;
BEGIN
  LOOP
    DELETE FROM large_table
     WHERE create_date < DATE '2023-01-01'  -- 利用日期索引
       AND ROWNUM <= 10000;                -- 每次提交 1 萬行
    v_deleted := v_deleted + SQL%ROWCOUNT;
    COMMIT;                                 -- 釋放 Undo，避免 ORA-01555
    EXIT WHEN SQL%ROWCOUNT = 0;
    DBMS_LOCK.SLEEP(0.1);                   -- 降低系統負載
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Total deleted: ' || v_deleted);
END;
/

關鍵點：

• 用 ROWNUM 限制單次事務規模。
• 利用「創建日期」索引快速定位目標行。
• 每批次 COMMIT 防止 Undo 爆滿。
• 適當 SLEEP 讓出 CPU/I/O 資源。

方案二：創建新表保留法（速度最快）

-- 1. 創建新表只保留需要的數據
CREATE TABLE large_table_keep AS
SELECT * FROM large_table
WHERE create_date >= DATE '2023-01-01';

-- 2. 重建索引、約束、授權
-- 3. 交換表名
RENAME large_table TO large_table_old;
RENAME large_table_keep TO large_table;

-- 4. 刪除舊表釋放空間
DROP TABLE large_table_old PURGE;

優勢：幾乎不產生 Redo/Undo，速度快數量級；注意：需停機維護窗口，處理外鍵依賴。

方案三：分區表交換（企業級最佳）

若表按日期範圍分區：

ALTER TABLE large_table DROP PARTITION p_2022_12 UPDATE GLOBAL INDEXES;

秒級完成，零業務影響，自動維護全局索引。

索引選擇建議

• 創建日期索引：適合按時間範圍刪除，範圍掃描效率高。
• Sequence ID 索引：若 ID 與時間單調遞增，也可用 WHERE seq_id < :max_id 分批刪除。
• 刪除期間暫停非必要索引維護：ALTER INDEX idx_name UNUSABLE; 刪除後 REBUILD。

事後維護

1. 降低 HWM：ALTER TABLE large_table ENABLE ROW MOVEMENT; ALTER TABLE large_table SHRINK SPACE;
2. 重建索引：ALTER INDEX idx_name REBUILD ONLINE;
3. 收集統計信息：EXEC DBMS_STATS.GATHER_TABLE_STATS('SCHEMA', 'LARGE_TABLE');

總結

5000 萬表刪 3000 萬，**分批循環刪除** 是無停機首選；有維護窗口則 **新表保留法** 最快；長期規劃請採用 **分區表**。切記：小事務、勤提交、利用索引、事後縮容。