SQL在进行外连接操作时，where子句中不能对内部表的字段进行限制，否则外连接会失效。
Oracle会将外连接直接转换为内连接。
测试步骤如下：
1.创建测试表
SQL> create table t
2 (a varchar2(10),
3 b varchar2(10));
Table created
SQL>
SQL> create table s
2 (a varchar2(10),
3 b varchar2(10));
Table created
2．检查外连接的执行计划（假设连接条件为t.a=s.a）
SQL> explain plan for
2 select t.a,s.*
3 from t left outer join s on (t.a=s.a) ;
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------Plan hash value: 269431714
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 21 | 5 (20)| 00:00:01 |
| 2 | TABLE ACCESS FULL| T | 1 | 7 | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| S | 1 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."A"="S"."A"(+))
3.当对外部表进行条件限制时，注意执行计划还是外连接的
SQL> explain plan for
2 select t.a,s.*
3 from t left outer join s on (t.a=s.a)
4 where t.a='aa';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------
Plan hash value: 269431714
---------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 21 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| S | 1 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."A"="S"."A"(+))
2 - filter("T"."A"='aa')
3 - filter("S"."A"(+)='aa')
4.当where子句中出现内部表的字段条件限制时，注意执行计划变化，已经不再是外连接，而是内连接了
SQL> explain plan for
2 select t.a,s.*
3 from t left outer join s on (t.a=s.a)
4 where s.a='aa';
Explained
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 59042520
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 21 | 5 (20)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 21 | 5 (20)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| T | 1 | 7 | 2 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| S | 1 | 14 | 2 (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T"."A"="S"."A")
2 - filter("T"."A"='aa')
3 - filter("S"."A"='aa')
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
- dynamic sampling used for this statement (level=2)
21 rows selected
