Oracle skip_unusable_indexes 參數對不可用索引行為的影響深度解析

參數定義

skip_unusable_indexes (預設 TRUE) 控制遭遇 UNUSABLE 索引時的 SQL 執行行為：

• TRUE：忽略不可用索引，改用全表掃描或其他可用存取路徑，**不報錯**，DML 正常執行但不維護該索引
• FALSE：嘗試使用不可用索引時拋出 ORA-01502 錯誤，DML 失敗

實測環境與步驟

-- 建立測試表與索引
CREATE TABLE t2 (sid INT NOT NULL, sname VARCHAR2(10)) TABLESPACE test;
BEGIN
  FOR i IN 1..100000 LOOP INSERT INTO t2 VALUES (i, 'ocpyang'); END LOOP;
  COMMIT;
END;
/
CREATE INDEX index_t2 ON t2(sid) TABLESPACE pindex;

-- 執行計畫基準：INDEX RANGE SCAN
SET AUTOTRACE ON EXPLAIN;
SELECT * FROM t2 WHERE sid < 10;

-- 設定索引 UNUSABLE
ALTER INDEX index_t2 UNUSABLE;

-- skip_unusable_indexes = TRUE (預設)
SELECT * FROM t2 WHERE sid < 10;  -- 執行計畫轉為 TABLE ACCESS FULL

-- skip_unusable_indexes = FALSE
ALTER SYSTEM SET skip_unusable_indexes = FALSE;
SELECT * FROM t2 WHERE sid < 10;  -- ORA-01502: index 'INDEX_T2' is in unusable state

執行計畫對比

DML 行為差異

• TRUE：INSERT/UPDATE/DELETE 成功執行，**不維護** UNUSABLE 索引，索引進一步損壞
• FALSE：DML 直接報錯失敗，保護索引一致性但影響可用性

運維建議

1. 預設保持 TRUE：避免單一壞索引導致整個應用癱瘓
2. 監控告警：定期查詢 DBA_INDEXES 篩選 STATUS = 'UNUSABLE'，納入巡檢
3. 修復策略：
   • 一般索引：ALTER INDEX ... REBUILD (ONLINE 選項減少鎖定)
   • 分區索引：ALTER INDEX ... REBUILD PARTITION ...
4. 謹慎設為 FALSE：僅在嚴格要求資料完整性、可接受停機修復的環境

相關參數

• optimizer_ignore_invalid_indexes (已棄用，12c+ 合併至 skip_unusable_indexes)
• _optimizer_skip_unusable_indexes (隱藏參數，勿手動修改)