[SQL] IN/NOT IN或EXISTS/NOT EXISTS在多數情況並沒有顯著效能的差異－米血的小天地

相信上過鼎X ERP技轉課或是有學過SQL的人都會認為使用EXISTS會比IN來得好，但其實這不是完全正確的。

尤其以SQL的可維護性角度來看，使用EXISTS比使用IN還來得不優，因若執行計畫出現Filter，則驅動表就會被固定，執行計畫也會被固定，也就是說無法透過執行計畫去優化SQL。而使用EXISTS這個固化子查詢關鍵字，如果又沒辦法對filter做unnest，則若優化器選到錯誤的驅動表，就會是很可怕的災難。

因IN所串連的子查詢WHERE子句不需要有PK的謂詞；EXISTS所串連的WHERE子句噓要有PK的謂詞連接，因此優化器只能將謂詞的連接設進filter。

(以上適用於使用Oracle 10g及以上的版本，且沒有改過優化器，即優化器是基於CBO(通常預設是CBO而不是RBO))

實驗如下：

SELECT imaa001 AS 料號,imaal003 AS 品名,imaal004 AS 規格,imaa006 AS 基礎單位,pmdb006 AS 請購量,sfac003 AS 工單在製量,qcba017 AS FQC在驗量
FROM (SELECT imaa001
,imaal003
,imaal004
,imaa006
,SUM(NVL(pmdb006,0)-NVL(pmdb049,0)) AS pmdb006
,SUM(NVL(sfac003,0)-NVL(sfac005,0)) AS sfac003
,SUM(NVL(qcba017,0)-NVL(qcba023,0)-NVL(qcba027,0)) AS qcba017
FROM imaa_t LEFT JOIN pmdb_t ON imaa001 = pmdb004 AND pmdbent = imaaent AND EXISTS (SELECT 1 FROM pmda_t WHERE pmdadocno = pmdbdocno AND pmdaent = pmdbent AND pmdaent = 168 AND pmdastus IN ('A','W','Y') AND pmdasite = 'PMTW')
LEFT JOIN imaal_t ON imaal001 = imaa001 AND imaalent = imaaent AND imaal002 = 'zh_TW' AND imaalent = 168
LEFT JOIN sfac_t ON sfac001 = imaa001 AND sfacent = imaaent AND EXISTS(SELECT 1 FROM sfaa_t WHERE sfaadocno = sfacdocno AND sfaaent = sfacent AND sfacent = 168 AND sfaa057 = '1' AND sfaastus = 'F' AND sfacsite = 'PMTW')
LEFT JOIN qcba_t ON qcba010 = imaa001 AND qcbaent = imaaent AND qcbaent = 168 AND qcbasite = 'PMTW' AND qcba000 = '2' AND qcbastus IN ('Y','N','F','A','D')
WHERE imaaent = 168
GROUP BY imaa001,imaal003,imaal004,imaa006
ORDER BY imaa001
) testa
;

統計資訊如下：

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24421 | 7774K| | 2049 (1)| 00:00:01 |
| 1 | VIEW | | 24421 | 7774K| | 2049 (1)| 00:00:01 |
| 2 | SORT GROUP BY | | 24421 | 5962K| 6528K| 2049 (1)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER | | 24421 | 5962K| | 728 (1)| 00:00:01 |
| 4 | VIEW | VW_DCL_F3A32432 | 1 | 40 | | 123 (1)| 00:00:01 |
|* 5 | HASH JOIN RIGHT SEMI | | 1 | 71 | | 123 (1)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED | SFAA_T | 72 | 1872 | | 54 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | SFAA_N2 | 646 | | | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | SFAC_T | 14171 | 622K| | 69 (2)| 00:00:01 |
|* 9 | HASH JOIN OUTER | | 24421 | 5008K| | 605 (1)| 00:00:01 |
|* 10 | HASH JOIN RIGHT OUTER | | 18879 | 1677K| | 331 (1)| 00:00:01 |
| 11 | VIEW | VW_DCL_F3A32432 | 1413 | 55107 | | 45 (0)| 00:00:01 |
|* 12 | HASH JOIN RIGHT SEMI | | 1413 | 96084 | | 45 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | PMDA_T | 413 | 11977 | | 11 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | PMDB_T | 3284 | 125K| | 34 (0)| 00:00:01 |
|* 15 | HASH JOIN RIGHT OUTER | | 18627 | 945K| | 285 (1)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID BATCHED| QCBA_T | 38 | 1292 | | 46 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | QCBA_N3 | 47 | | | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | IMAA_T | 18627 | 327K| | 239 (1)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | IMAAL_T | 18625 | 2164K| | 274 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ITEM_0"(+)="IMAA_T"."IMAA001" AND "ITEM_1"(+)="IMAA_T"."IMAAENT")
5 - access("SFAADOCNO"="SFACDOCNO" AND "SFAAENT"="SFACENT")
6 - filter("SFAA057"='1' AND "SFAAENT"=168)
7 - access("SFAASTUS"='F')
8 - filter("SFACSITE"='PMTW' AND "SFACENT"=168)
9 - access("IMAAL001"(+)="IMAA_T"."IMAA001" AND "IMAALENT"(+)="IMAA_T"."IMAAENT")
10 - access("IMAA001"="ITEM_2"(+) AND "ITEM_3"(+)="IMAAENT")
12 - access("PMDADOCNO"="PMDBDOCNO" AND "PMDAENT"="PMDBENT")
13 - filter("PMDASITE"='PMTW' AND ("PMDASTUS"='A' OR "PMDASTUS"='W' OR "PMDASTUS"='Y') AND "PMDAENT"=168)
14 - filter("PMDBENT"=168)
15 - access("QCBA010"(+)="IMAA_T"."IMAA001" AND "QCBAENT"(+)="IMAA_T"."IMAAENT")
16 - filter("QCBASITE"(+)='PMTW' AND ("QCBASTUS"(+)='A' OR "QCBASTUS"(+)='D' OR "QCBASTUS"(+)='F' OR
"QCBASTUS"(+)='N' OR "QCBASTUS"(+)='Y'))
17 - access("QCBAENT"(+)=168 AND "QCBA000"(+)='2')
18 - filter("IMAA_T"."IMAAENT"=168)
19 - filter("IMAAL002"(+)='zh_TW' AND "IMAALENT"(+)=168)

Statistics
-----------------------------------------------------------
8 CPU used by this session
8 CPU used when call started
8 DB time
3 Requests to/from client
2481 consistent gets
2 consistent gets examination
2 consistent gets examination (fastpath)
2481 consistent gets from cache
2479 consistent gets pin
2479 consistent gets pin (fastpath)
41 non-idle wait count
1 non-idle wait time
2 opened cursors cumulative
2 opened cursors current
2 pinned cursors current
2481 session logical reads
4 user calls

----------------------------------------------------------------------------------------------------

SELECT imaa001 AS 料號,imaal003 AS 品名,imaal004 AS 規格,imaa006 AS 基礎單位,pmdb006 AS 請購量,sfac003 AS 工單在製量,qcba017 AS FQC在驗量
FROM (SELECT imaa001
,imaal003
,imaal004
,imaa006
,SUM(NVL(pmdb006,0)-NVL(pmdb049,0)) AS pmdb006
,SUM(NVL(sfac003,0)-NVL(sfac005,0)) AS sfac003
,SUM(NVL(qcba017,0)-NVL(qcba023,0)-NVL(qcba027,0)) AS qcba017
FROM imaa_t LEFT JOIN pmdb_t ON imaa001 = pmdb004 AND pmdbent = imaaent AND pmdbdocno IN (SELECT pmdadocno FROM pmda_t WHERE pmdaent = pmdbent AND pmdaent = 168 AND pmdastus IN ('A','W','Y') AND pmdasite = 'PMTW')
LEFT JOIN imaal_t ON imaal001 = imaa001 AND imaalent = imaaent AND imaal002 = 'zh_TW' AND imaalent = 168
LEFT JOIN sfac_t ON sfac001 = imaa001 AND sfacent = imaaent AND sfacdocno IN (SELECT sfaadocno FROM sfaa_t WHERE sfaaent = sfacent AND sfacent = 168 AND sfaa057 = '1' AND sfaastus = 'F' AND sfacsite = 'PMTW')
LEFT JOIN qcba_t ON qcba010 = imaa001 AND qcbaent = imaaent AND qcbaent = 168 AND qcbasite = 'PMTW' AND qcba000 = '2' AND qcbastus IN ('Y','N','F','A','D')
WHERE imaaent = 168
GROUP BY imaa001,imaal003,imaal004,imaa006
ORDER BY imaa001
) testa
;

統計資訊如下：

---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24361 | 7755K| | 2045 (1)| 00:00:01 |
| 1 | VIEW | | 24361 | 7755K| | 2045 (1)| 00:00:01 |
| 2 | SORT GROUP BY | | 24361 | 5947K| 6512K| 2045 (1)| 00:00:01 |
|* 3 | HASH JOIN RIGHT OUTER | | 24361 | 5947K| | 728 (1)| 00:00:01 |
| 4 | VIEW | VW_DCL_F3A32432 | 76 | 3040 | | 123 (1)| 00:00:01 |
|* 5 | HASH JOIN | | 76 | 5396 | | 123 (1)| 00:00:01 |
|* 6 | TABLE ACCESS BY INDEX ROWID BATCHED | SFAA_T | 72 | 1872 | | 54 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | SFAA_N2 | 646 | | | 2 (0)| 00:00:01 |
|* 8 | TABLE ACCESS FULL | SFAC_T | 14171 | 622K| | 69 (2)| 00:00:01 |
|* 9 | HASH JOIN OUTER | | 24361 | 4995K| | 605 (1)| 00:00:01 |
|* 10 | HASH JOIN RIGHT OUTER | | 18832 | 1673K| | 331 (1)| 00:00:01 |
| 11 | VIEW | VW_DCL_F3A32432 | 1354 | 52806 | | 45 (0)| 00:00:01 |
|* 12 | HASH JOIN | | 1354 | 92072 | | 45 (0)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | PMDA_T | 413 | 11977 | | 11 (0)| 00:00:01 |
|* 14 | TABLE ACCESS FULL | PMDB_T | 3284 | 125K| | 34 (0)| 00:00:01 |
|* 15 | HASH JOIN RIGHT OUTER | | 18627 | 945K| | 285 (1)| 00:00:01 |
|* 16 | TABLE ACCESS BY INDEX ROWID BATCHED| QCBA_T | 38 | 1292 | | 46 (0)| 00:00:01 |
|* 17 | INDEX RANGE SCAN | QCBA_N3 | 47 | | | 2 (0)| 00:00:01 |
|* 18 | TABLE ACCESS FULL | IMAA_T | 18627 | 327K| | 239 (1)| 00:00:01 |
|* 19 | TABLE ACCESS FULL | IMAAL_T | 18625 | 2164K| | 274 (1)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

3 - access("ITEM_0"(+)="IMAA_T"."IMAA001" AND "ITEM_1"(+)="IMAA_T"."IMAAENT")
5 - access("SFACDOCNO"="SFAADOCNO" AND "SFAAENT"="SFACENT")
6 - filter("SFAA057"='1' AND "SFAAENT"=168)
7 - access("SFAASTUS"='F')
8 - filter("SFACSITE"='PMTW' AND "SFACENT"=168)
9 - access("IMAAL001"(+)="IMAA_T"."IMAA001" AND "IMAALENT"(+)="IMAA_T"."IMAAENT")
10 - access("IMAA001"="ITEM_2"(+) AND "ITEM_3"(+)="IMAAENT")
12 - access("PMDBDOCNO"="PMDADOCNO" AND "PMDAENT"="PMDBENT")
13 - filter("PMDASITE"='PMTW' AND ("PMDASTUS"='A' OR "PMDASTUS"='W' OR "PMDASTUS"='Y') AND "PMDAENT"=168)
14 - filter("PMDBENT"=168)
15 - access("QCBA010"(+)="IMAA_T"."IMAA001" AND "QCBAENT"(+)="IMAA_T"."IMAAENT")
16 - filter("QCBASITE"(+)='PMTW' AND ("QCBASTUS"(+)='A' OR "QCBASTUS"(+)='D' OR "QCBASTUS"(+)='F' OR
"QCBASTUS"(+)='N' OR "QCBASTUS"(+)='Y'))
17 - access("QCBAENT"(+)=168 AND "QCBA000"(+)='2')
18 - filter("IMAA_T"."IMAAENT"=168)
19 - filter("IMAAL002"(+)='zh_TW' AND "IMAALENT"(+)=168)

Statistics
-----------------------------------------------------------
8 CPU used by this session
8 CPU used when call started
8 DB time
3 Requests to/from client
2481 consistent gets
2 consistent gets examination
2 consistent gets examination (fastpath)
2481 consistent gets from cache
2479 consistent gets pin
2479 consistent gets pin (fastpath)
48 non-idle wait count
2 opened cursors cumulative
2 opened cursors current
2 pinned cursors current
2481 session logical reads
4 user calls