Oracle 效能優化操作 (15)：函數索引需等值匹配才生效

Oracle 效能優化操作 (15)：函數索引需等值匹配才生效

函數索引 (Function-Based Index) 能針對 `WHERE UPPER(name) = 'SMITH'` 這類查詢生效，但有一個嚴格限制：**查詢條件必須與索引定義的表達式完全等值匹配**，否則優化器會忽略索引退回全表掃描。

失效範例

-- 建立函數索引
CREATE INDEX idx_emp_name_substr ON emp(SUBSTR(ename,1,1));

-- 查詢：首字母為 'S'
SELECT * FROM emp WHERE SUBSTR(ename,1,1) = 'S';   -- ✅ 使用索引

-- 查詢：首字母不為 'S' (範圍/不等值)
SELECT * FROM emp WHERE SUBSTR(ename,1,1) <> 'S';  -- ❌ 全表掃描
SELECT * FROM emp WHERE SUBSTR(ename,1,1) > 'S';   -- ❌ 全表掃描

核心規則

• 索引定義表達式：SUBSTR(ename,1,1)
• 查詢條件必須為：SUBSTR(ename,1,1) = 常數 或 SUBSTR(ename,1,1) = 變數 (Bind Variable)。
• 任何隱含/顯含轉換、函數嵌套、運算子變更 (<>, >, LIKE) 都會導致失效。

優勢：避免計劃變更風險

傳統 B-Tree 索引一旦建立，所有針對該欄位的查詢可能受影響 (計劃變更)。函數索引只有「精確使用該函數表達式」的查詢才會被考慮，降低對現有應用的副作用，適合針對性優化特定問題 SQL。

最佳實踐

1. 確認問題 SQL 的確切 WHERE 子句表達式。
2. 建立完全匹配的函數索引。
3. 執行 DBMS_STATS.GATHER_TABLE_STATS(..., METHOD_OPT=>'FOR ALL HIDDEN COLUMNS') 收集虛擬欄位統計。
4. 驗證執行計劃出現 INDEX RANGE SCAN (或 INDEX UNIQUE SCAN)。