11gr2全外連線最佳化執行計劃

在11.2中，Oracle對於全外連線的執行計劃進行了最佳化。

 

 

在以前的版本中，全外連線的執行計劃如下：

SQL> SELECT * FROM V$VERSION;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.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 Database 11g Enterprise Edition Release 11.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中全外連線的邏輯讀減少了一半。