繞過函數導致索引失效：在 WHERE 子句中強制使用索引的實戰技巧

問題背景

WHERE 子句對欄位使用 UPPER、REPLACE、SUBSTR 等函數時，Oracle 優化器無法使用該欄位上的標準 B-tree 索引，導致全表掃描。

案例一：大小寫不敏感查詢 (UPPER)

原始查詢 (全表掃描)

SELECT address FROM address WHERE UPPER(name) LIKE '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%' 未使用函數，可走索引範圍掃描。
• 優化器對兩個 LIKE 範圍分別掃描索引，再 CONCATENATION 合併結果。
• 若資料量大，可擴展至 JO%、Jo%、jO% 等組合，進一步縮小掃描範圍。

案例二：電話號碼格式正規化 (REPLACE)

原始查詢

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

強制索引寫法

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

• 若含括號格式 (123) 456-7890，擴充條件：

  AND (phone_number LIKE '123%' OR phone_number LIKE '(123%')

• 關鍵：新增條件必須 不改變查詢語意，僅利用索引前導欄位特性縮小存取範圍。

核心原則

1. 識別 WHERE 子句中阻礙索引的函數。
2. 根據資料實際分布，設計 1~2 個無函數的 LIKE 'prefix%' 或 = 'value' 條件。
3. 以 AND 連接原條件與新條件，確保結果集不變。
4. 驗證執行計畫出現 INDEX RANGE SCAN + CONCATENATION。

適用場景與限制

• ✅ 字元型欄位 (CHAR/VARCHAR2/CLOB) 且資料前導字元具區辨度。
• ✅ 函數為單向轉換 (大小寫、去符號、截取前綴)。
• ❌ 數值/日期型欄位 (隱含轉換不同)。
• ❌ 函數為雙向或複雜邏輯 (如 DECODE、CASE)。
• ⚠️ 非萬能替代方案：長期應考慮函數索引 (FBI) 或虛擬欄位索引 (11g+)。