Oracle 索引 UNUSABLE 與 INVISIBLE 差異解析：空間釋放、DML 維護與優化器可見性

Oracle 索引 UNUSABLE vs INVISIBLE 差異解析

11g 引入 INVISIBLE 索引，配合既有 UNUSABLE 狀態，提供靈活的索引管理策略。本文對比兩者在空間、維護、優化器可見性上的差異。

對照表

適用場景

• UNUSABLE：
  • 大量載入資料前標記 UNUSABLE，載入完 REBUILD，避免索引維護開銷。
  • 永久廢棄的索引，釋放空間後再決定是否重建。
• INVISIBLE：
  • 測試移除索引對查詢計劃影響：設 INVISIBLE → 觀察效能 → 決定 DROP 或 VISIBLE。
  • 暫時隱藏索引供特定 Session 測試 (設 OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE)。

操作範例

-- 標記 UNUSABLE
ALTER INDEX idx_name UNUSABLE;
-- 檢查狀態
SELECT index_name, status FROM user_indexes WHERE index_name='IDX_NAME';  -- UNUSABLE
-- 重建
ALTER INDEX idx_name REBUILD;

-- 設定 INVISIBLE
ALTER INDEX idx_name INVISIBLE;
-- Session 啟用
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES=TRUE;
-- 恢復可見
ALTER INDEX idx_name VISIBLE;