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



在以前的版本中，全外连接的执行计划如下：

SQL> SELECT * FROM V$VERSION;

BANNER

----------------------------------------------------------------

Oracle Database10gEnterpriseEdition Release10.2.0.3.0 - 64bi

PL/SQL Release 10.2.0.3.0 - Production

CORE    10.2.0.3.0      Production

TNS for Linux: Version 10.2.0.3.0 - Production

NLSRTL Version 10.2.0.3.0 - Production

SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(30));

Table created.

SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30));

Table created.

SQL> INSERT INTO T1

 2  SELECT ROWNUM - 1, OBJECT_NAME

 3  FROM USER_OBJECTS

 4  WHERE ROWNUM < 10;

9 rows created.

SQL> INSERT INTO T2

 2  SELECT ROWNUM + 1, OBJECT_NAME

 3  FROM USER_OBJECTS

 4  WHERE ROWNUM < 10;

9 rows created.

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

        1

        0

                  10

                   9

11 rows selected.


Execution Plan

----------------------------------------------------------

Plan hash value: 2841162349

-----------------------------------------------------------------------------

| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------

|   0 | SELECT STATEMENT     |      |    10 |   260 |    13   (8)| 00:00:01 |

|   1 |  VIEW                |      |    10 |   260 |    13   (8)| 00:00:01 |

|   2 |   UNION-ALL          |      |       |       |            |          |

|*  3 |    HASH JOIN OUTER   |      |     9 |   234 |     7  (15)| 00:00:01 |

|   4 |     TABLE ACCESS FULL| T1   |     9 |   117 |     3   (0)| 00:00:01 |

|   5 |     TABLE ACCESS FULL| T2   |     9 |   117 |     3   (0)| 00:00:01 |

|*  6 |    HASH JOIN ANTI    |      |     1 |    26 |     7  (15)| 00:00:01 |

|   7 |     TABLE ACCESS FULL| T2   |     9 |   117 |     3   (0)| 00:00:01 |

|   8 |     TABLE ACCESS FULL| T1   |     9 |   117 |     3   (0)| 00:00:01 |

-----------------------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

  3 - access("T1"."ID"="T2"."ID"(+))

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

Note

-----

  - dynamic sampling used for this statement


Statistics

----------------------------------------------------------

         0  recursive calls

         0  db block gets

        29  consistent gets

         0  physical reads

         0  redo size

       718  bytes sent via SQL*Net to client

       492  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

        11  rows processed

全外连接由一个外连接和一个反连接UNION ALL来获得，因此每张表必须扫描两次。

而在11.2中，执行计划为：

SQL> SELECT * FROM V$VERSION;

BANNER

--------------------------------------------------------------------------------

Oracle Database11gEnterprise Edition Release11.2.0.1.0 - 64bit 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

SQL> CREATE TABLE T1 (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> CREATE TABLE T2 (ID NUMBER, NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T1

 2  SELECT ROWNUM - 1, OBJECT_NAME

 3  FROM USER_OBJECTS

 4  WHERE ROWNUM < 10;

已创建9行。

SQL> INSERT INTO T2

 2  SELECT ROWNUM + 1, OBJECT_NAME

 3  FROM USER_OBJECTS

 4  WHERE ROWNUM < 10;

已创建9行。

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

       519  bytes received via SQL*Net from client

         2  SQL*Net roundtrips to/from client

         0  sorts (memory)

         0  sorts (disk)

        11  rows processed

Oracle优化了全外连接的执行计划，通过HASH JOIN FULL OUTER执行计划，使得每个表仅扫描一次，对比两个版本的逻辑读也可以看出，在11.2中全外连接的逻辑读减少了一半。



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