详解表连接顺序和连接方式是否影响查询结果

两张表连接时，不论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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/750077/viewspace-1768408/