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