您现在的位置：程式碼之家 >> 數據庫知識 > >> Oracle 資料庫 >> Oracle 教學

Oracle 效能優化技巧：函數索引解決 WHERE 子句函數導致全表掃描

編輯：Oracle 教學

問題場景

任何對欄位施加函數運算 (如 SUBSTR, UPPER, TRUNC, TO_CHAR) 都會導致該欄位上的一般 B-tree 索引失效，迫使優化器選擇全表掃描。

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

驗證執行計畫

CREATE [UNIQUE | BITMAP] INDEX index_name
ON table_name (function_expression [, function_expression ...])
[TABLESPACE tablespace_name]
[COMPRESS [ADVANCED | LOW | HIGH]]
[PARALLEL degree]
[NOLOGGING];

支援的函數類型

內建 SQL 函數：UPPER, LOWER, SUBSTR, TRUNC, ROUND, TO_CHAR, TO_DATE, NVL, DECODE, CASE 等
使用者自訂函數 (UDF)：**必須宣告為 DETERMINISTIC**

CREATE INDEX emp_ename_upper_idx ON emp (UPPER(ename));
-- 查詢
SELECT * FROM emp WHERE UPPER(ename) = 'SMITH';

2. 日期欄位範圍查詢 (避免 TRUNC)

CREATE INDEX emp_name_job_idx 
ON emp (UPPER(ename), UPPER(job));

-- 可支援查詢
WHERE UPPER(ename) = 'SMITH' AND UPPER(job) = 'CLERK';
WHERE UPPER(ename) = 'SMITH';  -- 前導欄位匹配可用

限制與注意事項

限制	說明	對策
查詢條件需精確匹配	WHERE 子句函數運算式須與索引定義完全一致	建立程式碼規範，統一函數寫法
UDF 必須 DETERMINISTIC	非確定性函數無法建立索引	確保函數無副作用、相同輸入必得相同輸出
增加 DML 開銷	INSERT/UPDATE 需額外計算函數值維護索引	評估讀寫比，高寫入表慎用
統計資訊需同步	函數索引也需收集統計資訊	DBMS_STATS.GATHER_TABLE_STATS(cascade=>TRUE)
不支援 BITMAP 函數索引	僅 B-tree 支援	低基數欄位考慮虛擬欄位 + 一般索引