Oracle 強制索引技巧：WHERE 條件含函數時的索引穿透方案

前言

Oracle 優化器在 WHERE 條件對列使用函數 (UPPER、REPLACE、SUBSTR、TRUNC 等) 時，會導致該列索引失效，退化為全表掃描。本文揭示「增加額外篩選條件強制索引範圍掃描」的核心技巧，並給出大小寫混合、電話號碼格式化、日期截斷三大典型場景完整解決方案。

一、為什麼函數會導致索引失效？

索引存儲的是列的原始值 (如 'john')。查詢 WHERE UPPER(name) = 'JOHN' 時，優化器無法將 'JOHN' 與索引中的 'john' 直接比較，因為函數改變了比較值的形態。優化器只能全表掃描，對每行計算 UPPER(name) 再比較。

二、核心技巧：添加「不改變結果」的索引可用條件

在 WHERE 子句中 AND 額外條件，該條件：

1. 直接引用原始列 (無函數)，優化器可用索引範圍掃描。
2. 邏輯上被原條件隱含 (不改變結果集)。
3. 將大範圍掃描縮小為多個小範圍 (CONCATENATION 計畫)。

三、場景一：大小寫混合查詢 (UPPER/LOWER)

-- 原查詢：全表掃描
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%');

-- 進一步細化：前兩字母組合
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%');

執行計畫變化：
全表掃描 → CONCATENATION → 多個 INDEX RANGE SCAN → TABLE ACCESS BY INDEX ROWID。
每個 LIKE 條件觸發一次索引範圍掃描，結果合併。

四、場景二：電話號碼格式化查詢 (REPLACE)

數據格式多樣：123-456-7890, 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%')

五、場景三：日期截斷查詢 (TRUNC/TO_CHAR)

-- 查詢某天的訂單
WHERE TRUNC(order_date) = DATE '2024-01-15'

-- 強制索引：添加日期範圍
WHERE TRUNC(order_date) = DATE '2024-01-15'
  AND order_date >= DATE '2024-01-15'
  AND order_date <  DATE '2024-01-16'  -- 開區間避免時分秒問題

原則：日期範圍條件 (>= <) 可直接使用索引，且不改變結果。

六、進階：函數索引 (Oracle 8i+ / 12c+ 增強)

若頻繁對函數結果查詢，可直接在函數表達式上建索引：

CREATE INDEX idx_upper_name ON address (UPPER(name));
CREATE INDEX idx_trunc_order_date ON orders (TRUNC(order_date));
CREATE INDEX idx_clean_phone ON customers (REPLACE(REPLACE(phone, '-', ''), ' ', ''));

-- 查詢自動使用函數索引
SELECT * FROM address WHERE UPPER(name) = 'JOHN';

優勢：無需修改 SQL、索引維護由資料庫自動、支援複雜表達式。
劣勢：插入/更新開銷增加、統計信息需定期收集、佔用額外空間。

七、適用邊界與注意事項

• **僅適用於字符/數值類型**，LOB、XMLType 等複雜類型無效。
• **額外條件必須精準**：過寬 (如 LIKE '%') 無效；過窄可能漏數據 (邏輯錯誤)。
• **成本評估**：若表極小 (幾百行)，全表掃描反而更快，優化器可能忽略索引提示。
• **現代替代方案**：虛擬列 + 索引 (11g+)、函數索引、應用層存儲標準化值 (如統一存大寫、純數字電話)。

八、總結

Oracle WHERE 條件含函數導致索引失效，核心破解法是「AND 原始列範圍條件」。三大場景模板：
1. 大小寫 → LIKE 'J%' OR LIKE 'j%'
2. 格式化 → LIKE '純數字前綴%'
3. 日期截斷 → >= 當天 00:00 AND < 明天 00:00
長期治理建議：入庫標準化 + 函數索引/虛擬列，從源頭消除函數查詢需求。