Oracle 千萬級大表刪除與修改優化實戰：索引失效與並行操作技巧

問題現象

某生產環境需刪除 SH30_PRCSS_EXCTN_LOG 表中 2010 年前的 19 萬筆資料（全表 34 萬筆）。原本執行 DELETE 陳述式超過半小時未完成，被迫中止。執行計劃顯示走 INDEX RANGE SCAN，但統計資訊過期導致優化器誤判，實際應採用全表掃描並行處理。

優化步驟

1. 將所有索引標記為 UNUSABLE

ALTER INDEX IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX2 UNUSABLE;
ALTER INDEX IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX3 UNUSABLE;
ALTER INDEX IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX4 UNUSABLE;
ALTER INDEX IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX5 UNUSABLE;
ALTER INDEX IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX6 UNUSABLE;

此步驟避免刪除過程中維護索引產生大量 REDO/UNDO 開銷。

2. 使用並行提示執行刪除

DELETE /*+ PARALLEL(e,8) */ 
FROM idwsq11.SH30_PRCSS_EXCTN_LOG e
WHERE deriv_prcss_time_stamp < TO_DATE('2010-JAN-01', 'YYYY-MON-DD');
COMMIT;

8 並行度對應 8 核 CPU，刪除 19 萬筆僅耗時 7 秒。

3. 線上重建索引（NOLOGGING + 並行）

ALTER INDEX IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX2 REBUILD ONLINE NOLOGGING PARALLEL;
ALTER INDEX IDWSQ11.SH30_PRCSS_EXCTN_LOG_IDX3 REBUILD ONLINE NOLOGGING PARALLEL;
...（其餘索引同理）

重建總耗時約 3 分鐘，整體流程 2 分鐘內完成。

核心原則總結

• 大量 DELETE/UPDATE 前：檢視執行計劃，評估索引 vs 全表掃描；若走全表掃描較佳，先 UNUSABLE 索引，操作完成後 REBUILD。
• 刪除操作額外建議：執行 ALTER TABLE ... MOVE 降低高水位（HWM），再重建索引。
• 大量資料載入：UNUSABLE 索引 → INSERT /*+ APPEND PARALLEL NOLOGGING */ → REBUILD 索引。

實戰數據載入範例

INSERT /*+ APPEND PARALLEL NOLOGGING */ 
INTO ADWGU_DICFR.DICFR_SALES_FCT 
SELECT /*+ PARALLEL(e,8) */ * 
FROM adwgq_dicfr.DICFR_SALES_FCT@adw1d e;

實測 1.1 億筆跨 DB Link 載入，耗時 1 小時 10 分。並行度建議設為 CPU 核心數。