Oracle 索引失效解決方案：WHERE 子句使用函數時強制優化器使用索引

問題背景：函數導致索引失效

當 WHERE 子句對欄位套用 UPPER、REPLACE、SUBSTR 等函數時，Oracle 優化器無法使用該欄位上的索引，導致全表掃描，查詢效能大幅下降。

核心技巧：增加額外條件「欺騙」優化器

在不改變原查詢語意的前提下，於 WHERE 子句加入一或多個「測試索引值」的條件，縮小掃描範圍，強制優化器走索引範圍掃描。

案例一：大小寫混合查詢 (UPPER)

原查詢 (全表掃描)：

SELECT address FROM address WHERE UPPER(name) LIKE 'JOHN';
-- Execution Plan: TABLE ACCESS FULL ADDRESS

優化後 (索引範圍掃描)：

SELECT address FROM address 
WHERE UPPER(name) LIKE 'JOHN' 
  AND (name LIKE 'J%' OR name LIKE 'j%');
-- Execution Plan: INDEX RANGE SCAN ADDRESS_I (透過 CONCATENATION 合併兩個範圍)

進階寫法可列出所有大小寫組合 (JO%, jo%, Jo%, jO%)，產生 4 個 INDEX RANGE SCAN 再合併，效能更佳。

案例二：電話號碼格式不一 (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%')

關鍵原則

• 新增條件不改變查詢結果，僅協助優化器縮小搜尋範圍。
• 條件需基於對資料格式的完整理解（如：電話號碼可能的前綴模式）。
• 一或兩個字元的前綴條件通常已足夠大幅提升速度，避免 WHERE 子句過於龐大。