Oracle 索引失效七大常見原因與解決對策

Oracle 索引失效七大常見原因與解決對策

明明建了索引，執行計畫卻走 Full Table Scan？本文整理七大導致索引失效的典型模式與對應解法。

七大失效原因

1. 優化器模式：Instance 級 OPTIMIZER_MODE=ALL_ROWS 傾向全掃描。
   → 修改 init.ora 為 RULE 或 CHOOSE，或用 Hint /*+ FIRST_ROWS */。
2. 統計資訊過舊/缺失：最常見原因。
   → DBMS_STATS.GATHER_TABLE_STATS 定期收集。
3. 表太小：優化器判斷 Full Scan 成本更低。
   → 正常現象，無需強制索引。
4. 複合索引未用到首欄：INDEX SKIP SCAN 條件嚴格，通常不會觸發。
   → 查詢條件必須包含索引首欄。
5. 含 NULL 欄位的索引：SELECT COUNT(*) 不會使用該索引（B-tree 不存 NULL）。
   → 改用 COUNT(1) 或建立函數索引 CREATE INDEX idx ON t(NVL(col,0))。
6. 索引欄位使用函數/運算：WHERE UPPER(name)='ABC'、WHERE sal+100>2000。
   → 建立函數索引 CREATE INDEX idx ON t(UPPER(name)) 或改寫條件。
7. 隱式型別轉換：WHERE char_col = 123 觸發 TO_NUMBER(char_col)=123。
   → 條件值與欄位型別保持一致。
8. 日期截斷導致失效：WHERE TRUNC(dt)=TRUNC(SYSDATE)。
   → 改寫為範圍：WHERE dt >= TRUNC(SYSDATE) AND dt < TRUNC(SYSDATE)+1。
9. 不等號 <> / NOT IN / LIKE '%abc'：無法走索引範圍掃描。
   → 改寫邏輯或使用全文索引。

判斷原則

並非所有情況用索引都比 Full Scan 快（Full Scan 採多區塊讀取）。先優化 SQL、收集統計、確認選擇率，證實索引確實更快後再用 Hint 強制。