詳解表連線順序和連線方式是否影響查詢結果

兩張表連線時，不論A連線B表，還是B表連線A表，連線結果的總數都不會改變，但如果多張表連線，甚至又有外聯結時連線結果的總數是否會發生變化，且原因是什麼，帶著這個問題抓取產品庫中的一條典型SQL進一步分析。
有三張表HQ_READ.UP_LOAD_SERLNO_0721 ，MCS_HQ.HI_SALE_CHNL ，MCS_HQ.MA_CHNL 分別叫T1，T2,T3
，T1裡有23980條資料，如果按T1外聯結T2,再內連結T3的順序結果是4356條資料，
但現在按照自己的設想，SQL不變，但改變表連線順序，看看是否會改變查詢結果中資料的總數。查詢結果設想已T1的資料為準，表連線順序是T2，T3表自然連線後再與T1做外連線，總數是T1表的資料條數23980，所以我用hint方式改變表連線順序
執行計劃也顯示T2和T3內連結，再和T1外聯結，因此我認為最後外聯結出來的資料應該是23980條資料，但結果依然是4356條資料，我們看詳細的執行計劃，如下列出了兩種情況，例1是T1，T2外聯結後再與T3做自然連線的順序對應的執行計劃，例2是用hint改變了連線順序T2，T3自然連線後再與T1外聯結對應的執行計劃。

例1 未改變表連線順序，預計4356條資料，實際4356條資料
SQL> SELECT COUNT(*)
2 FROM MCS_HQ_READ.UP_LOAD_SERLNO_0721 T1
3 ,MCS_HQ.HI_SALE_CHNL T2
4 ,MCS_HQ.MA_CHNL T3
5 WHERE T1.SERL_NO = T2.SERL_NO(+)
6 AND T2.UPPR_SHOP_SUPP_CHNL_ID = T3.CHNL_ID
7 ;

執行計劃
----------------------------------------------------------
Plan hash value: 3070468476
--------------------------------------------------------------------------------
-----------------------------------------------------
| Id | Operation | Name | Rows | By
tes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
138 | | 66465 (1)| 00:13:18 | | |
| 1 | SORT AGGREGATE | | 1 |
138 | | | | | |
|* 2 | HASH JOIN | | 32555 | 4
387K| 4456K| 66465 (1)| 00:13:18 | | |
| 3 | NESTED LOOPS | | |
| | | | | |
| 4 | NESTED LOOPS | | 32555 | 4
069K| | 65162 (1)| 00:13:02 | | |
| 5 | TABLE ACCESS FULL | UP_LOAD_SERLNO_0721 | 32555 | 3
242K| | 22 (0)| 00:00:01 | | |
|* 6 | INDEX UNIQUE SCAN | UX_HI_SALE_CHNL_1 | 1 |
| | 1 (0)| 00:00:01 | | |
| 7 | TABLE ACCESS BY GLOBAL INDEX ROWID| HI_SALE_CHNL | 1 |
26 | | 2 (0)| 00:00:01 | ROWID | ROWID |
| 8 | INDEX FAST FULL SCAN | PK_MA_CHNL | 582K| 5
685K| | 473 (1)| 00:00:06 | | |
--------------------------------------------------------------------------------
-----------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."UPPR_SHOP_SUPP_CHNL_ID"="T3"."CHNL_ID")
6 - access("T1"."SERL_NO"="T2"."SERL_NO")
Note
-----
- dynamic sampling used for this statement (level=2)

統計資訊
----------------------------------------------------------
17 recursive calls
0 db block gets
60464 consistent gets
1661 physical reads
764 redo size
235 bytes sent via SQL*Net to client
240 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
4 sorts (memory)
0 sorts (disk)
1 rows processed

例2 hint改變表連線順序，預計結果行數23890 實際4356
SQL> SELECT /*+ LEADING(T2,T3,T1) USE_HASH(T2,T3) USE_HASH(T1) */
2 COUNT(*)
3 FROM MCS_HQ_READ.UP_LOAD_SERLNO_0721 T1
4 ,MCS_HQ.HI_SALE_CHNL T2
5 ,MCS_HQ.MA_CHNL T3
6 WHERE T1.SERL_NO = T2.SERL_NO(+)
7 AND T2.UPPR_SHOP_SUPP_CHNL_ID = T3.CHNL_ID
8 /

執行計劃
----------------------------------------------------------
Plan hash value: 4158379028
--------------------------------------------------------------------------------
---------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc|
Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------
---------------------------------------
| 0 | SELECT STATEMENT | | 1 | 138 | |
94889 (1)| 00:18:59 | | |
| 1 | SORT AGGREGATE | | 1 | 138 | |
| | | |
|* 2 | HASH JOIN | | 32555 | 4387K| 3632K|
94889 (1)| 00:18:59 | | |
| 3 | TABLE ACCESS FULL | UP_LOAD_SERLNO_0721 | 32555 | 3242K| |
22 (0)| 00:00:01 | | |
|* 4 | HASH JOIN | | 6585K| 226M| 238M|
79676 (1)| 00:15:57 | | |
| 5 | PARTITION RANGE ALL | | 6585K| 163M| |
66675 (1)| 00:13:21 | 1 | 50 |
| 6 | TABLE ACCESS FULL | HI_SALE_CHNL | 6585K| 163M| |
66675 (1)| 00:13:21 | 1 | 50 |
| 7 | INDEX FAST FULL SCAN| PK_MA_CHNL | 582K| 5685K| |
473 (1)| 00:00:06 | | |
--------------------------------------------------------------------------------
---------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T1"."SERL_NO"="T2"."SERL_NO")
4 - access("T2"."UPPR_SHOP_SUPP_CHNL_ID"="T3"."CHNL_ID")
Note
-----
- dynamic sampling used for this statement (level=2)

統計資訊
----------------------------------------------------------
7 recursive calls
0 db block gets
316739 consistent gets
314220 physical reads
0 redo size
216 bytes sent via SQL*Net to client
240 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
實際的結果都是一樣的，說明表連線順序不會影響實際結果，但為什麼不會影響呢？如果把同樣的SQL改成子查詢的方式
SELECT
       COUNT(*)
     FROM MCS_HQ_READ.UP_LOAD_SERLNO_0721 T1
         ,(SELECT *
          FROM MCS_HQ.HI_SALE_CHNL T2
             ,MCS_HQ.MA_CHNL T3
          WHERE  T2.UPPR_SHOP_SUPP_CHNL_ID = T3.CHNL_ID
         ) T2
WHERE  T1.SERL_NO = T2.SERL_NO(+)
查詢結果總數就是我們想要的按T1表的資料總數顯示。
通過對比我們發現在多表連線時，雖然表連線順序改變了，但連線條件沒有改變WHERE T1.SERL_NO = T2.SERL_NO(+)
   AND   T2.UPPR_SHOP_SUPP_CHNL_ID = T3.CHNL_ID
多表連線的條件如上兩個條件，在最終3個表連線後的結果集中都要滿足的資料才能顯示，如果按T2，T3連線，再和T3外聯結的結果，還需要同時滿足如上兩個條件，但實際是T1.SERL_NO = T2.SERL_NO(+) 而 相應資料的T2.UPPR_SHOP_SUPP_CHNL_ID ！= T3.CHNL_ID
如T1的SERL_NO是10，T2的SERL_NO 是null，而對應T2中的 UPPR_SHOP_SUPP_CHNL_ID 也是null，那麼T3中的CHNL_ID沒有null，上面兩個條件就不同時滿足，因此表連線順序受連線條件限制不會影響查詢結果。
細心的朋友也會看出在多表連線時的執行計劃中並沒有出現外聯結的計劃項，說明優化器早已判斷出T2.UPPR_SHOP_SUPP_CHNL_ID = T3.CHNL_ID是自然連線，只選擇非空做運算，如果是外連線的話，不匹配的T3的所有列都為空的。外聯結是無意義的，所以計劃並沒有使用類似join outer 之類的詞。優化器自動作出了相應優化

 

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/750077/viewspace-1768408/，如需轉載，請註明出處，否則將追究法律責任。