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


11 + 9 =