Oracle 字串搜尋效能實測：INSTR 完勝 LIKE，1100 萬筆實測差距 3 倍

實測場景

• 測試表 T，資料量 1,100 萬筆
• 欄位 TITLE 進行關鍵字「手冊」搜尋
• 環境：Oracle 資料庫，啟用 SET TIMING ON

實測結果對比

等價邏輯對照

• INSTR(col, 'str') > 0 ≡ col LIKE '%str%'
• INSTR(col, 'str') = 0 ≡ col NOT LIKE '%str%'

深度原理解析

1. LIKE 前導萬用字元 (%str%) 導致全表掃描：無法使用 B-tree 索引，優化器只能選擇 Full Table Scan，且需逐行進行模式匹配運算。
2. INSTR 為內建 C 函數：執行路徑極短，CPU 開銷極低，同樣全表掃描下，純運算效能遠優於 LIKE 的正則式匹配引擎。
3. 結果集大小不影響 INSTR 相對優勢：無論命中少（6.5 萬）或多（1155 萬），INSTR 穩定在 11 秒級，LIKE 則隨掃描量線性增長。

進階優化策略

1. Oracle Text 全文索引（根治方案）

CREATE INDEX idx_title_ctx ON t(title) INDEXTYPE IS CTXSYS.CONTEXT;
-- 查詢
SELECT * FROM t WHERE CONTAINS(title, '手冊') > 0;

支援語詞、語幹、近鄰、模糊等豐富語義查詢，毫秒級回應。

2. 函數索引（輕量替代）

CREATE INDEX idx_title_instr ON t(INSTR(title, '手冊'));
-- 查詢需完全匹配索引表達式
SELECT * FROM t WHERE INSTR(title, '手冊') > 0;

適用於固定關鍵字高頻查詢，避免全表掃描。

3. 虛擬欄位 + 索引（11g+）

ALTER TABLE t ADD (has_manual AS (CASE WHEN INSTR(title, '手冊')>0 THEN 1 ELSE 0 END));
CREATE INDEX idx_has_manual ON t(has_manual);

適用場景決策樹

• 臨時/低頻/小表（<10 萬）→ 直接 LIKE 可接受
• 固定關鍵字/高頻/中表 → INSTR + 函數索引
• 多關鍵字/語義/大表 → Oracle Text 全文索引
• 需排序/分頁/聚合 → 結合上述索引策略

避坑指南

• ❌ 不要在 WHERE 條件對大表欄位使用 LOWER/UPPER/TRIM/SUBSTR 等函數導致索引失效
• ❌ 不要盲目相信「LIKE 後置萬用字元 (str%) 可用索引」——中文語境下語詞邊界不明，前置萬用字元極常見
• ✅ 使用 EXPLAIN PLAN 驗證執行計畫是否為 INDEX RANGE SCAN
• ✅ 定期 ANALYZE / DBMS_STATS 更新統計資訊，確保優化器決策準確

1100 萬筆實測鐵證：INSTR 以 3 倍效能優勢碾壓 LIKE。字串搜尋優化，首選內建函數，次選函數索引，終局 Oracle Text。