Oracle 11g 隱形索引：索引生命週期管理的終極利器

傳統痛點

• DROP INDEX：不可逆，重建大表耗時數小時。
• ALTER INDEX ... UNUSABLE：索引停止維護，重新啟用需 REBUILD 全量掃描，同樣耗時。

隱形索引機制

• 定義：CREATE INDEX ... INVISIBLE 或 ALTER INDEX ... INVISIBLE。
• 優化器行為：CBO/RBO 完全忽略，執行計劃不考慮。
• DML 維護：INSERT/UPDATE/DELETE 仍同步更新索引結構，零效能損耗。
• 狀態切換：ALTER INDEX ... VISIBLE 瞬間生效，無重建、無統計重收集。

實測對比

-- 可見索引：走 INDEX RANGE SCAN，Cost 14
SELECT * FROM t_test1 WHERE table_name LIKE 'HR%';

ALTER INDEX t_test1_idx INVISIBLE;

-- 隱形後：走 FULL TABLE SCAN，Cost 25
SELECT * FROM t_test1 WHERE table_name LIKE 'HR%';

-- 秒級還原
ALTER INDEX t_test1_idx VISIBLE;
-- Elapsed: 00:00:00.01

受控啟用驗證

-- Session 級啟用
ALTER SESSION SET optimizer_use_invisible_indexes = TRUE;

-- Hint 強制（需參數為 TRUE）
SELECT /*+ INDEX(t_test1 t_test1_idx) */ * FROM t_test1 WHERE table_name LIKE 'HR%';

注意：參數預設 FALSE，Hint 單獨無效，必須配合參數。

適用場景

1. 廢棄索引觀察期：MONITORING USAGE 確認無用 → INVISIBLE 觀察 1~2 週 → DROP。
2. 新索引上線驗證：CREATE ... INVISIBLE → 測試環境開啟參數驗證計劃 → 生產 VISIBLE。
3. 緊急回滾：新索引上線後引發計劃劣化，秒級 INVISIBLE 止血。

查詢隱形索引

SELECT index_name, visibility FROM user_indexes WHERE visibility = 'INVISIBLE';