在文章《【FULL OUTER JOIN】全外连接的union all改写方法》（http://space.itpub.net/519536/viewspace-618053）中给大家介绍过关于全外连接的使用方法及改写方法。Oracle 11gR2版本中使用了全新的执行计划Full outer Joins来支持全外连接查询，提高了检索效率。这里对比一下union写法和全外连接写法他们的执行计划的不同。

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

Good luck.

secooler
11.11.01

-- The End --

ITPub博客

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

最新文章