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

兩張表連接配接時，不論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|

| 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| |

| 7 | INDEX FAST FULL SCAN| PK_MA_CHNL | 582K| 5685K| |

473 (1)| 00:00:06 | | |

2 - access("T1"."SERL_NO"="T2"."SERL_NO")

4 - access("T2"."UPPR_SHOP_SUPP_CHNL_ID"="T3"."CHNL_ID")

7 recursive calls

316739 consistent gets

314220 physical reads

0 redo size

216 bytes sent via SQL*Net to client

0 sorts (memory)

實際的結果都是一樣的，說明表連接配接順序不會影響實際結果，但為什麼不會影響呢？如果把同樣的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 之類的詞。優化器自動作出了相應優化