11gr2全外连接优化执行计划（三）

在11.2中，Oracle对于全外连接的执行计划进行了优化。

这篇进一步介绍NATIVE_FULL_OUTER_JOIN提示。

11gr2全外连接优化执行计划：http://yangtingkun.itpub.net/post/468/506826

11gr2全外连接优化执行计划（二）：http://yangtingkun.itpub.net/post/468/506876

虽然上一篇介绍了NATIVE_FULL_OUTER_JOIN和NO_NATIVE_FULL_OUTER_JOIN两个HINT，但是实际上NATIVE_FULL_OUTER_JOIN并没有发挥任何的作用，因为Oracle对全外连接的优化使得新的执行计划的代价比原始执行计划要低，所以Oracle默认就选择这个执行计划，因此看不到NATIVE_FULL_OUTER_JOIN提示的效果。

SQL> SET AUTOT ON
SQL> SELECT T1.ID, T2.ID
  2  FROM T1 FULL OUTER JOIN T2
  3  ON T1.ID = T2.ID;

        ID         ID
---------- ----------
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
                    9
                   10
         1
         0

已选择11行。

执行计划
----------------------------------------------------------
Plan hash value: 53297166

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     9 |   234 |     9  (12)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |     9 |   234 |     9  (12)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |     9 |   234 |     9  (12)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T1       |     9 |   117 |     4   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | T2       |     9 |   117 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="T2"."ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

尝试在RBO情况下执行：

SQL> ALTER SESSION SET OPTIMIZER_MODE = CHOOSE;

会话已更改。

SQL> SELECT T1.ID, T2.ID
  2  FROM T1 FULL OUTER JOIN T2
  3  ON T1.ID = T2.ID;

        ID         ID
---------- ----------
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8

已选择7行。

执行计划
----------------------------------------------------------
Plan hash value: 3374424389

-----------------------------------------
| Id  | Operation            | Name     |
-----------------------------------------
|   0 | SELECT STATEMENT     |          |
|   1 |  VIEW                | VW_FOJ_0 |
|   2 |   MERGE JOIN         |          |
|   3 |    SORT JOIN         |          |
|   4 |     TABLE ACCESS FULL| T2       |
|*  5 |    SORT JOIN         |          |
|   6 |     TABLE ACCESS FULL| T1       |
-----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   5 - access("T1"."ID"="T2"."ID")
       filter("T1"."ID"="T2"."ID")

Note
-----
   - rule based optimizer used (consider using cbo)

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        700  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          7  rows processed

SQL> SELECT /*+ NATIVE_FULL_OUTER_JOIN */ T1.ID, T2.ID
  2  FROM T1 FULL OUTER JOIN T2
  3  ON T1.ID = T2.ID;

        ID         ID
---------- ----------
         2          2
         3          3
         4          4
         5          5
         6          6
         7          7
         8          8
                    9
                   10
         1
         0

已选择11行。

执行计划
----------------------------------------------------------
Plan hash value: 53297166

----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     9 |   234 |     9  (12)| 00:00:01 |
|   1 |  VIEW                 | VW_FOJ_0 |     9 |   234 |     9  (12)| 00:00:01 |
|*  2 |   HASH JOIN FULL OUTER|          |     9 |   234 |     9  (12)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | T1       |     9 |   117 |     4   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | T2       |     9 |   117 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("T1"."ID"="T2"."ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

统计信息
----------------------------------------------------------
          7  recursive calls
          0  db block gets
         31  consistent gets
          0  physical reads
          0  redo size
        733  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         11  rows processed

RBO下显然不会出现最新的执行计划，而加上NATIVE_FULL_OUTER_JOIN提示后，执行计划变为新的外连接。

需要注意RBO的执行计划和运行结果都是错误的，这里是个bug，详细描述可以参考：http://yangtingkun.itpub.net/post/468/507056