1.创建实验表并初始化实验数据
sys@ora11g> select * from v$version;
BANNER
------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
conn sec/sec
create table a (a number(1),b number(1),c number(1));
create table b (a number(1),d number(1),e number(1));
insert into a values(1,1,1);
insert into a values(2,2,2);
insert into a values(3,3,3);
insert into b values(1,4,4);
insert into b values(2,5,5);
insert into b values(4,6,6);
commit;
2.两种实现全外连接查询的方法
1）第一种方法：使用两次外连接结合union的方法
select * from a,b where a.a=b.a(+)
union
select * from a,b where a.a(+)=b.a;
2）第二种方法：使用全外连接标准写法
select * from a full outer join b on a.a = b.a;
3.比较两种执行方法的执行计划
1）反复执行第一种方法得到稳定的执行计划
sec@ora11g> select * from a,b where a.a=b.a(+)
2 union
3 select * from a,b where a.a(+)=b.a;
A B C A D E
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 4 4
2 2 2 2 5 5
3 3 3
4 6 6
Execution Plan
----------------------------------------------------------
Plan hash value: 891669117
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6 | 468 | 15 (60)| 00:00:01 |
| 1 | SORT UNIQUE | | 6 | 468 | 15 (60)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | HASH JOIN OUTER | | 3 | 234 | 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| A | 3 | 117 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| B | 3 | 117 | 3 (0)| 00:00:01 |
|* 6 | HASH JOIN OUTER | | 3 | 234 | 7 (15)| 00:00:01 |
| 7 | TABLE ACCESS FULL| B | 3 | 117 | 3 (0)| 00:00:01 |
| 8 | TABLE ACCESS FULL| A | 3 | 117 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("A"."A"="B"."A"(+))
6 - access("A"."A"(+)="B"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
28 consistent gets
0 physical reads
0 redo size
792 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
1 sorts (memory)
0 sorts (disk)
4 rows processed
2）反复执行第二种方法得到稳定的执行计划
sec@ora11g> select * from a full outer join b on a.a = b.a;
A B C A D E
---------- ---------- ---------- ---------- ---------- ----------
1 1 1 1 4 4
2 2 2 2 5 5
4 6 6
3 3 3
Execution Plan
----------------------------------------------------------
Plan hash value: 3456740935
----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 234 | 7 (15)| 00:00:01 |
| 1 | VIEW | VW_FOJ_0 | 3 | 234 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN FULL OUTER| | 3 | 234 | 7 (15)| 00:00:01 |
| 3 | TABLE ACCESS FULL | A | 3 | 117 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL | B | 3 | 117 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("A"."A"="B"."A")
Note
-----
- dynamic sampling used for this statement (level=2)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
15 consistent gets
0 physical reads
0 redo size
792 bytes sent via SQL*Net to client
419 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
3）执行计划对比结果
第一种外连接结合union方法对表进行了两次扫描，而全外连接方法引入了HASH JOIN FULL OUTER执行计划仅需对表进行一次扫描便得到了查询结果。从consistent gets上也很直观，全外连接写法（28）比union方法（15）的consistent gets少了一半。
4.小结
Oracle对全外连接的支持越来越好，从最初Oracle不支持全外连接的SQL写法，到支持SQL的特定写法，同时从执行计划上也在逐渐优化中。