Oracle 函數索引：解決函數運算導致全表掃描的利器

痛點：函數運算讓索引失效

當 WHERE 條件對欄位施加函數運算時，Oracle 無法使用該欄位上的一般 B-tree 索引，導致全表掃描：

-- 即使 ename 有索引，仍會全表掃描
SELECT * FROM emp WHERE SUBSTR(ename, 1, 2) = 'SM';

解決方案：函數索引

在索引定義中直接包含函數運算式，Oracle 會預先計算並儲存運算結果，查詢時若條件完全匹配，即可使用索引：

CREATE INDEX emp_ename_substr_idx ON emp (SUBSTR(ename, 1, 2));

再次執行上述查詢，執行計劃將顯示 INDEX RANGE SCAN。

關鍵限制：精確匹配原則

函數索引只有在查詢條件與索引定義的運算式完全一致時才會被使用。例如：

-- 使用索引
WHERE SUBSTR(ename, 1, 2) = 'SM'

-- 不使用索引（長度不同）
WHERE SUBSTR(ename, 1, 1) = 'S'

優勢：不影響其他查詢計劃

一般新增索引可能導致無關查詢執行計劃變更（优化器誤判選擇率），但函數索引只有在出現「完全相同的函數運算式」時才被考慮，風險極低，適合針對性優化固定查詢模式。

實務建議

• 針對高頻、固定模式的函數查詢（如 UPPER(col) = ?、TRUNC(date_col) = ?、SUBSTR(col,1,n) = ?）建立函數索引。
• 避免過度建立：每個索引都會增加 DML 維護成本。
• 11g 後可用 DBMS_STATS.GATHER_TABLE_STATS 收集函數索引統計資訊，提升優化器決策準確度。

驗證腳本

EXPLAIN PLAN FOR SELECT * FROM emp WHERE SUBSTR(ename,1,2)='SM';
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
-- 觀察 Operation 欄是否為 INDEX RANGE SCAN