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

这篇进一步介绍NATIVE_FULL_OUTER_JOIN提示。


虽然上一篇介绍了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提示后，执行计划变为新的外连接。


oracle视频教程请关注:http://u.youku.com/user_video/id_UMzAzMjkxMjE2.html