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中全外連線的邏輯讀減少了一半。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28628435/viewspace-1984431/，如需轉載，請註明出處，否則將追究法律責任。

11gr2全外連線優化執行計劃

相關文章