關於SQL語法IN條件式的效能
前些時候，在做資料查詢時發現ORACLE支援多欄位IN條件的簡化寫法:
SELECT * FROM T1 WHERE (C1,C2) IN (SELECT C1,C2 FROM T2)
當下覺得它好簡潔，SQL沒有真是可惜，便在噗浪上嘟嚷了兩句，獲得一些回饋: 保哥提到SQL有EXISTS可以取代，hector ♂ lee則提醒IN可能存在的效能疑慮... 而這些資訊恰巧幫助我對於IN條件查詢效能有了新的體認，讓我不禁要說: 有網路社群真好!
應用上次學會的簡潔IN多欄位寫法，我搞出了類似以下的SQL語句。主要目的是要搜尋BOM資料表中有哪些零件不存在於PARTS資料表中，由於零件是用VENDER, CATG, PARTID三個當Key，所以就很直覺地用了(P_VENDER, P_CATG, P_PARTID) not in (select VENDER, CATG, PARTID from PARTS)，看起來挺漂亮的，沒想到是金玉其外，敗絮其中! BOM約五千筆，PARTS約兩萬筆，查詢竟足足跑了兩分半才結束。
select * from
(
select
M_VENDOR, M_MODEL, P_VENDOR, P_CATG, P_PARTID
from BOM
) X
where (P_VENDOR, P_CATG, P_PARTID)
not in (select VENDOR, CATG, PARTID from PARTS)
於是我試著用NOT EXISTS改寫:
select * from
(
select
M_VENDOR, M_MODEL, P_VENDOR, P_CATG, P_PARTID
from BOM
) X
where not exists (
select VENDOR, CATG, PARTID from PARTS
where VENDOR = P_VENDOR and CATG = P_CATG and PARTID = P_PARTID
)
神奇了，只要1秒鐘就得到相同的結果。
回頭想想，PARTS的VENDOER, CATG, PARTID是PK，VENDOR = P_VENDOR and CATG = P_CATG and PARTID = P_PARTID的比對條件肯定可以使用Primary Key Index；而在IN條件式裡，select VENDOR, CATG, PARTID from PARTS產生的是一個沒有Index的暫時Table，比對時只能有Table Scan，這是我揣測二者效能差異的原因。
叫出執行計劃來比對，上方的表格是IN條式件，下方則是NOT EXISTS版本。其中的差別在於INDEX FAST FULL SCAN對上INDEX SKIP SCAN(成本 31 vs 2)，導致總成本相差了12倍(3432 vs 276)。
| Description | Object Owner |
Object name |
Cost | Cardinality | Bytes |
| SELECT STATEMENT, GOAL = ALL_ROWS | 3432 | 220 | 5280 | ||
| FILTER | |||||
| TABLE ACCESS FULL | JEFF | BOM | 56 | 220 | 5280 |
| INDEX FAST FULL SCAN | JEFF | PARTS_PK | 31 | 1 | 21 |
| Description | Object Owner |
Object name |
Cost | Cardinality | Bytes |
| SELECT STATEMENT, GOAL = ALL_ROWS | 276 | 220 | 5280 | ||
| FILTER | |||||
| TABLE ACCESS FULL | JEFF | BOM | 56 | 220 | 5280 |
| INDEX SKIP SCAN | JEFF | PARTS_PK | 2 | 1 | 21 |
提醒自己，下回要再使用IN當作查詢條件，要當心掉入Table Scan的效能陷阱!!
留言列表
