Oracle 索引失效十大常見原因與排查步驟 Oracle 索引失效十大常見原因與排查步驟

明明建了索引，執行計劃卻走 Full Table Scan。依照以下十步排查，覆蓋 95% 索引失效場景。

排查清單

1. 優化器模式：SHOW PARAMETER optimizer_mode。若為 RULE 或統計資訊缺失導致 CBO 退化，索引可能被忽略。
2. WHERE 條件是否包含索引前導欄：複合索引 (a,b,c)，查詢必須包含 a (或 a,b)，否則索引無法使用。
3. 連接方式：Nested Loop 才能有效利用內表索引；Hash Join / Sort Merge Join 對索引幫助有限。
4. 連接順序：驅動表 (Outer) 全表掃描，被驅動表 (Inner) 才用索引。若大表在外，索引無用。
5. 資料字典表/視圖：未分析統計資訊，易產生劣質計劃。**切勿自行分析 SYS 物件**，改用 Hint 或改寫。
6. 隱性型別轉換：WHERE char_col = 123 → 內部 TO_NUMBER(char_col) 導致索引失效。統一使用 WHERE char_col = '123'。
7. 統計資訊過期：EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'EMP', cascade=>TRUE); 定期收集。
8. 索引選擇性過低：欄位基數極小 (如性別 M/F)，CBO 認為全表掃描更快。可建立直方圖：METHOD_OPT=>'FOR COLUMNS SIZE 254 sex'。
9. 索引欄位可為 NULL：COUNT(*)、WHERE col IS NULL (無索引) 不走索引。建立函數索引 CREATE INDEX idx_emp_nvl ON emp(NVL(deptno, -1)) 或強制 NOT NULL。
10. 並行查詢 (PQ)：PQ 不使用 B-tree 索引。若需強制索引：SELECT /*+ INDEX(emp idx_emp_dept) */ * FROM emp WHERE deptno=10;

驗證工具鏈

-- 1. AUTOTRACE 快速看計劃
SET AUTOTRACE ON EXPLAIN
SELECT ...;

-- 2. TKPROF 深度分析
EXEC DBMS_SYSTEM.SET_SQL_TRACE_IN_SESSION(sid, serial#, TRUE);
-- 跑完 SQL
tkprof trace.trc output.txt EXPLAIN=user/pass

Hint 強制索引語法

SELECT /*+ INDEX(t idx_emp_dept) */ * FROM emp t WHERE deptno = 10;
-- 注意：/*+ 緊貼 SELECT，無空格

最佳實踐

• 建立「每週統計收集 + 每月索引使用率報表」機制。
• 新上線 SQL 必須經 EXPLAIN PLAN 驗證索引使用情況。
• 函數索引、部分索引 (12c)、隱形索引 (11g) 善用解決特殊場景。

十步排查法，讓索引失效無處遁形。