测试步骤如下：
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.测试在left outer情况下，且外部表的有条件限制的执行计划
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')
注意，条件限制filter("T"."A"='aa')和filter("S"."A"(+)='aa')是出现在全表扫描时的。
3.如果将上面查询，其它环节都不变，仅仅换成full outer join
SQL> explain plan for
2 select t.a,s.*
3 from t full 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: 2625355656
———————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time
———————————————————————
| 0 | SELECT STATEMENT | | 1 | 21 | 5 (20)| 00:00:01
|* 1 | VIEW | VW_FOJ_0 | 1 | 21 | 5 (20)| 00:00:01
|* 2 | HASH JOIN FULL OUTER| | 1 | 21 | 5 (20)| 00:00:01
| 3 | TABLE ACCESS FULL | T | | | 2 (0)| 00:00:01
| 4 | TABLE ACCESS FULL | S | 1 | 14 | 2 (0)| 00:00:01
———————————————————————
Predicate Information (identified by operation id):
—————————————————
1 – filter("T"."A"='aa')
2 – access("T"."A"="S"."A")
注意，全表扫描时不再应用filter("T"."A"='aa')的限制条件。
FULL OUTER JOIN的WHERE子句条件会出现在VIEW视图中。
ORACLE会先将FULL OUTER JOIN得出结果（视图），然后从视图应用条件filter("T"."A"='aa')来获得最终结果集。
