Oracle 效能優化：LIKE 查詢優先前綴匹配啟用索引範圍掃描

核心原則

LIKE 子句能否使用索引，關鍵在於萬用字元 (%) 位置：

• 前綴匹配：LIKE 'ABC%' → 可使用索引 (INDEX RANGE SCAN)
• 後綴/中間匹配：LIKE '%ABC' 或 LIKE '%ABC%' → 無法使用索引 (FULL TABLE SCAN)

實戰對比

無法使用索引：前置萬用字元

SELECT * FROM city WHERE name LIKE 'S%';

執行計畫：INDEX RANGE SCAN on CITY_NAME_IDX — 索引有序特性支援範圍查找。

為什麼前綴匹配可用索引？

1. B-tree 索引按欄位值排序儲存。
2. 'S%' 代表所有以 'S' 開頭的字串，在索引中形成連續範圍。
3. 優化器可直接定位範圍起始與結束位置，進行範圍掃描。

常見場景與改寫技巧

1. 固定前綴代碼查詢 (最佳)

-- 前端傳入 'Tai' → 後端拼接 'Tai%'
WHERE city_name LIKE :input || '%'

3. 後綴/中間匹配的替代方案

4. 反向索引實作範例

表大小FULL SCAN 時間INDEX RANGE SCAN 時間加速倍數
10 萬筆~50 ms~2 ms25x
100 萬筆~500 ms~5 ms100x
1000 萬筆~5 秒~20 ms250x


最佳實踐建議

業務設計階段：規劃查詢模式，優先支援前綴匹配 (如代碼、編號、前綴搜尋)。
索引建立：針對高頻 LIKE 查詢欄位建立 B-tree 索引。
避免雙向萬用字元：LIKE '%keyword%' 於大表嚴禁，改用 Oracle Text。
統計資訊：確保欄位統計資訊最新，優化器才能正確選擇索引。
參數化查詢：使用綁定變數 LIKE :b1 || '%'，利用游標共享。


總結
LIKE 查詢索引生效鐵律：前綴固定，後綴靠邊。 設計階段即規劃前綴查詢模式，配合 B-tree 索引，可獲得數量級效能提升。後綴/中間匹配需引入反向索引、Oracle Text 或架構層面方案，切勿在大表上直接使用 LIKE '%...%'。