ORACLE LIKE效能調校經驗一枚

在我的認知裡，資料庫查詢在使用LIKE 'ABC%'時還是可以引用Index，但若變成LIKE '%ABC%'，或LIKE '%ABC'，就註定只能Table Scan，把整張資料表的每一筆撈出來比對。

最近幫忙看一個Oracle資料庫查詢效能不佳的個案，學到不同的處理經驗:

SELECT C1, C2, C3, C4, C5, C6, C7 FROM myTable
WHERE C1 = 'XXXX'
AND C2 LIKE 'ABC%'
AND C3 BETWEEN TO_DATE('2010/11/01','yyyy/mm/dd') AND TO_DATE('2010/11/30','yyyy/mm/dd')
AND C4 = 'ZZZZ'

myTable已針對C1, C2, C3, C4, C5設了Index，由於資料筆數高達數百萬筆，上述的查詢要耗上30秒才會有結果。使用Explain Plan發現它動用了Table Scan，大概就是速度爆慢的原因。

在網路上爬文，看到一些關於Oracle LIKE效能的討論，提到了Text Index及Reverse Key Index的技巧，但看來都是針對'%XX'或'%XX%'的情境。而Oracle的文件也提到:

Pattern Matching on Indexed Columns

When you use LIKE to search an indexed column for a pattern, Oracle can use the index to improve performance of a query if the leading character in the pattern is not % or _. In this case, Oracle can scan the index by this leading character. If the first character in the pattern is % or _, then the index cannot improve performance because Oracle cannot scan the index.

由此看來，LIKE 'ABC%'應該可以適用Index加速才合理。但實測結果，就是擺脫不了Table Scan跟慢吞吞的命運。

山不轉路轉，想到了一點。由於C2雖然使用了LIKE比對，但其會出現的值都列在另一個Table C2List中，所以我可以先在C2List用LIKE出對所有可能的結果，再用IN當查詢條件。所以查詢可改為:

SELECT C1, C2, C3, C4, C5, C6, C7 FROM myTable
WHERE C1 = 'XXXX'
AND C2 IN (SELECT C2 FROM C2List WHERE C2 LIKE 'ABC%')
AND C3 BETWEEN TO_DATE('2010/11/01','yyyy/mm/dd') AND TO_DATE('2010/11/30','yyyy/mm/dd')
AND C4 = 'ZZZZ'

咻!! 改良版的查詢不到1秒就彈回結果。檢查Explain Plan，的確加速的原因來自引用了Index。

雖然為什麼LIKE 'ABC%'一定要Table Scan的原因仍是個謎，還是把這次經驗寫下來留作參考。