Oracle外连接等价于内连接的情况
Oracle外连接等价于内连接的情况
实际之前有过研究，Oracle外连接的执行计划中没有出现OUTER的例子，现在展开来讲。
以左连接为例子，有SQL如下：
左边的执行计划中id=2并不带有OUTER，表示不是外连接。
右边的执行计划和左边的执行计划一模一样，证明左边的SQL完全等价与右边的SQL。
select count(*) from t left join tt on t.col = tt.col where tt.col='b'; select count(*) from t,tt where t.col = tt.col and tt.col='b'; Plan hash value: 3743140366 Plan hash value: 3743140366 ----------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | ----------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 51 | | | | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 51 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 51 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 51 | | | | |* 2 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 51 | 2616K| 2616K| 793K (0)| |* 2 | HASH JOIN | | 1 | 1 | 1 |00:00:00.01 | 51 | 2616K| 2616K| 794K (0)| |* 3 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 45 | | | | |* 3 | TABLE ACCESS FULL| T | 1 | 1 | 1 |00:00:00.01 | 45 | | | | |* 4 | TABLE ACCESS FULL| TT | 1 | 1 | 1 |00:00:00.01 | 6 | | | | |* 4 | TABLE ACCESS FULL| TT | 1 | 1 | 1 |00:00:00.01 | 6 | | | | ----------------------------------------------------------------------------------------------------------------- ----------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): Predicate Information (identified by operation id): --------------------------------------------------- --------------------------------------------------- 2 - access("T"."COL"="TT"."COL") 2 - access("T"."COL"="TT"."COL") 3 - filter("T"."COL"='b') 3 - filter("T"."COL"='b') 4 - filter("TT"."COL"='b') 4 - filter("TT"."COL"='b')
Oracle外连接可以等价改写为内连接的前提条件，当被驱动表的字段出现在where中并且不能为is null（如果是连接列is null是另外一种情况：反连接）。
原因是外连接中驱动表进行谓词条件过滤后的全部的数据都会出现在最终结果集中，不满足连接条件的时候被驱动表的字段均为null，这个时候被驱动表的字段出现在where（如上边的tt.col='b'）表示这一部分数据肯定是满足连接条件的，那么自然可以改写为内连接。
不能改写的例子防偷：
select count(*) from t left join tt on t.col = tt.col where tt.name is null; select count(*) from t left join tt on t.col = tt.col where tt.col is null; Plan hash value: 2949581252 Plan hash value: 3556837987 --------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | --------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.02 | 51 | | | | | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 51 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.02 | 51 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 51 | | | | |* 2 | FILTER | | 1 | | 1 |00:00:00.02 | 51 | | | | |* 2 | HASH JOIN RIGHT ANTI| | 1 | 1000K| 1 |00:00:00.01 | 51 | 2616K| 2616K| 897K (0)| |* 3 | HASH JOIN RIGHT OUTER| | 1 | 1000K| 24578 |00:00:00.02 | 51 | 2061K| 2061K| 833K (0)| | 3 | TABLE ACCESS FULL | TT | 1 | 1000K| 2 |00:00:00.01 | 6 | | | | |* 4 | TABLE ACCESS FULL | TT | 1 | 1 | 2 |00:00:00.01 | 6 | | | | | 4 | TABLE ACCESS FULL | T | 1 | 1000K| 24578 |00:00:00.01 | 45 | | | | | 5 | TABLE ACCESS FULL | T | 1 | 1000K| 24578 |00:00:00.01 | 45 | | | | ------------------------------------------------------------------------------------------------------------------- --------------------------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): Predicate Information (identified by operation id): --------------------------------------------------- --------------------------------------------------- 2 - access("T"."COL"="TT"."COL") 2 - filter("TT"."NAME" IS NULL) 3 - access("T"."COL"="TT"."COL") 4 - filter("TT"."COL" IS NOT NULL)
