执行计划】Oracle 11gR2使用Full outer Joins执行计划完成全外连接查询

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的特定写法，同时从执行计划上也在逐渐优化中。