3

前些時候，在做資料查詢時發現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)。

DescriptionObject
Owner		Object
name		CostCardinalityBytes
SELECT STATEMENT, GOAL = ALL_ROWS  34322205280
  FILTER     
    TABLE ACCESS FULLJEFFBOM562205280
    INDEX FAST FULL SCANJEFFPARTS_PK 31121

 

DescriptionObject
Owner		Object
name		CostCardinalityBytes
SELECT STATEMENT, GOAL = ALL_ROWS  2762205280
  FILTER     
    TABLE ACCESS FULLJEFFBOM562205280
    INDEX SKIP SCANJEFFPARTS_PK 2121

提醒自己，下回要再使用IN當作查詢條件，要當心掉入Table Scan的效能陷阱!!


Comments

# by R.

這事當年我們討論過, 大哥您忘啦:D

# by Jeffrey

to R., 到了這把年紀，只有承認"小時候的事"早就忘得差不多了 XD

# by William

應該可不需要多一層 Selece (......) X Where ..... not ...

Post a comment