在我的認知裡,資料庫查詢在使用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的原因仍是個謎,還是把這次經驗寫下來留作參考。


Comments

# by Daniel

請問你有去analyze C2List 這個table 及其index嗎? 如果有statistics 結果可能會不一樣喔

# by Daniel

請問你有去analyze C2List 這個table 及其index嗎? 如果有statistics 結果可能會不一樣喔

# by Jeffrey

to Daniel, 補充一下,依我了解資料庫有設排程每週一次analyze各資料表及index,而C2List的資料筆數不超過200筆。

# by nolem

可以貼原始很慢的plan 參考一下嗎? in 改用exists 會好很多(因為你C2List筆數少不然會很慘) 真的找不出原因可以用hint 強迫index 方式執行... Index Hints usage 如下 /*+ INDEX ( table [index [index]...] ) */

# by Nick

猜測和查詢的值可能有關 C2 LIKE 'ABC%'和C2 LIKE 'ABCDEFGHIJK%'效能可能會有差異 myTable有固定在analyze的話,可能oracle分析後'ABC%'與table scan的cost差異不大,猜測可能是ABC開頭的筆數不多或該欄位值較大查詢值太短等原因 有測試過oracle對like '0%'和'00%',like '0%'是table scan,like '00%'是走index

Post a comment