Oracle 函數索引失效？教你用「輔助條件」強制走索引

問題背景

在 WHERE 子句對欄位使用 UPPER、REPLACE、SUBSTR 等函數時，Oracle 優化器無法直接使用該欄位上的標準 B-tree 索引，導致全表掃描，效能大幅下降。本文介紹一種不修改原查詢邏輯、僅透過增加輔助條件即可強制使用索引的技巧。

案例一：大小寫混合資料查詢

現象

假設 ADDRESS 表的 NAME 欄位存有大小寫混合資料，需查詢名為 'john' 的記錄：

SELECT address FROM address WHERE UPPER(name) LIKE 'JOHN';

執行計畫顯示 TABLE ACCESS FULL ADDRESS，索引未被使用，因為索引儲存的是原始值 'john'，而非轉大寫後的 'JOHN'。

解決方案：增加輔助條件

SELECT address 
FROM address 
WHERE UPPER(name) LIKE 'JOHN' 
  AND (name LIKE 'J%' OR name LIKE 'j%');

新增的條件 (name LIKE 'J%' OR name LIKE 'j%') 不含函數，優化器可對 NAME 索引進行兩次 INDEX RANGE SCAN，再透過 CONCATENATION 合併結果。執行計畫顯示：

CONCATENATION
  TABLE ACCESS BY INDEX ROWID ADDRESS
    INDEX RANGE SCAN ADDRESS_I
  TABLE ACCESS BY INDEX ROWID ADDRESS
    INDEX RANGE SCAN ADDRESS_I

進階優化：縮小掃描範圍

若資料量龐大，可擴充輔助條件至更多字元組合：

SELECT address 
FROM address 
WHERE UPPER(name) LIKE 'JOHN' 
  AND (name LIKE 'JO%' OR name LIKE 'jo%' OR name LIKE 'Jo%' OR name LIKE 'jO%');

這會產生四個 INDEX RANGE SCAN，進一步減少掃描行數。實務上指定 1-2 個字元通常已足夠，無需窮盡所有大小寫組合。

案例二：電話號碼格式不一

電話號碼可能以 123-456-7890、(123) 456-7890 等格式儲存。查詢時常用 REPLACE 移除非數字字元：

WHERE REPLACE(REPLACE(phone_number, '-'), ' ') = '1234567890'

此寫法同樣導致索引失效。強制索引寫法：

WHERE REPLACE(REPLACE(phone_number, '-'), ' ') = '1234567890'
  AND phone_number LIKE '123%'

若含括號，輔助條件需涵蓋兩種開頭格式：

WHERE REPLACE(REPLACE(REPLACE(REPLACE(phone_number, '-'), ' '), '('), ')') = '1234567890'
  AND (phone_number LIKE '123%' OR phone_number LIKE '(123%')

核心原則

1. 輔助條件必須不含函數，直接參考原始欄位值。
2. 輔助條件不能改變查詢結果，僅作為索引存取路徑的導引。
3. 根據實際資料分佈設計條件，需熟悉欄位中可能出現的格式變化。

總結

當 WHERE 子句因業務需求必須使用函數時，透過增加一到兩個精心設計的輔助條件，即可誘導優化器使用索引，大幅提升查詢效能，且不改變原有業務邏輯。這是 Oracle 效能調校中極具實戰價值的技巧。