Oracle 執行計畫不走索引？七大核心原因與解決方案完整總結

現象：明明有索引，執行計畫卻選 FULL TABLE SCAN

這是 Oracle 效能調校最常見的困惑。優化器基於成本模型（CBO）決策，「不走索引」通常意味著優化器認為全表掃描成本更低，而非優化器有 Bug。以下系統梳理七大核心原因與對應策略。

原因一：優化器模式與統計資訊——最常見元兇

原因二：複合索引首欄未用——Index Skip Scan 限制

-- 索引定義
CREATE INDEX idx_emp_dept_job ON emp(deptno, job);

-- 查詢條件缺少首欄 deptno
SELECT * FROM emp WHERE job = 'CLERK';
-- ❌ 通常不走索引（除非啟用 INDEX SKIP SCAN 且首欄基數極低）

-- 正確用法
SELECT * FROM emp WHERE deptno = 20 AND job = 'CLERK';  -- ✅ 索引範圍掃描
SELECT * FROM emp WHERE deptno = 20;                    -- ✅ 索引範圍掃描

補救：若業務頻繁單獨查 job，建立單獨索引 CREATE INDEX idx_emp_job ON emp(job) 或調整複合索引欄位順序（高選擇性欄位前置）。

原因三：NULL 值與 COUNT(*) 陷阱

-- 含 NULL 欄位的索引
CREATE INDEX idx_emp_comm ON emp(comm);  -- comm 允許 NULL

-- 不走索引
SELECT COUNT(*) FROM emp;                    -- 全表掃描
SELECT COUNT(comm) FROM emp;                 -- 走索引（只數非 NULL）
SELECT COUNT(*) FROM emp WHERE comm IS NOT NULL; -- 走索引

原理：B-Tree 索引不存儲全 NULL 的鍵值。COUNT(*) 需統計所有列，優化器無法確信索引覆蓋全表，故選 FTS。

原因四：索引列上使用函數/運算式——隱性轉換殺手

原因五：隱性型別轉換——字串比對數字、日期格式不符

-- 欄位 VARCHAR2，傳入數字
SELECT * FROM emp WHERE empno = 7369;  
-- Oracle 隱性轉換：TO_NUMBER(empno) = 7369 → 索引失效

-- 正確：傳入字串
SELECT * FROM emp WHERE empno = '7369';  -- 或使用參數綁定

-- 日期隱性轉換陷阱
SELECT * FROM emp WHERE hiredate = '1980-12-17';  -- 依賴 NLS_DATE_FORMAT
-- 安全寫法
SELECT * FROM emp WHERE hiredate = DATE '1980-12-17';  -- ANSI 日期字面值
SELECT * FROM emp WHERE hiredate = TO_DATE('1980-12-17','YYYY-MM-DD');

原因六：不等號與 LIKE 前置萬用字元

-- 不等號通常不走索引（範圍太大）
SELECT * FROM emp WHERE deptno <> 20;  
-- 改寫：UNION ALL 兩個範圍
SELECT * FROM emp WHERE deptno < 20
UNION ALL
SELECT * FROM emp WHERE deptno > 20;

-- LIKE '%pattern' 開頭萬用字元 → 索引失效
SELECT * FROM emp WHERE ename LIKE '%SMITH';  -- 全表掃描
-- 對策：Oracle Text / 反向索引 / 函數索引 REVERSE(ename) / 應用層前綴搜尋

原因七：優化器成本誤判——FTS 真比索引快

當查詢回傳資料佔表總量 > 5%-10% 時，FTS 多區塊讀（DB_FILE_MULTIBLOCK_READ_COUNT）通常比「索引範圍掃描 + 大量單區塊回表」更快。不要盲目強制索引，先驗證實際執行時間與邏輯讀（SET AUTOTRACE TRACEONLY）。

系統性排查與解決流程

1. 確認統計資訊最新
   EXEC DBMS_STATS.GATHER_TABLE_STATS(ownname=>'SCOTT', tabname=>'EMP', cascade=>TRUE);

2. 觀察實際執行計畫（非 EXPLAIN PLAN）
   SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'ALLSTATS LAST'));

3. 對比邏輯讀
   SET AUTOTRACE TRACEONLY STATISTICS
   -- 執行原 SQL 與強制索引 Hint 版本，對比 consistent gets

4. 若統計正確但計畫仍差 → 考慮
   • SQL Profile / SQL Plan Baseline 固定好計畫
   • 擴展統計資訊（欄位群組相關性）
   • 直方圖（高傾斜欄位）
   • 重寫 SQL（避開函數/隱性轉換）

5. 最後手段：Hint 強制（註解說明原因）
   SELECT /*+ INDEX(emp idx_emp_deptno) */ * FROM emp WHERE deptno = 20;

總結

「不走索引」90% 歸因於統計資訊不準、SQL 寫法阻礙索引使用（函數/轉換/首欄缺失）、優化器正確選擇 FTS。建立「統計資訊定時收集 → SQL 規範避開函數/隱性轉換 → 執行計畫定期巡檢」三層防線，比事後強制 Hint 更根本、更穩健。