在文章《【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 --

【執行計劃】Oracle 11gR2使用Full outer Joins執行計劃完成全外連線查詢

相關文章