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

Appleses發表於2016-01-30

11.2中,Oracle對於全外連線的執行計劃進行了優化。

這篇介紹新增的兩個相關的HINT

11gr2全外連線優化執行計劃:http://yangtingkun.itpub.net/post/468/506826

 

 

Oracle在推出了新的執行計劃的同時,還提供了兩個控制這個執行計劃的提示NATIVE_FULL_OUTER_JOINNO_NATIVE_FULL_OUTER_JOIN

這兩個HINT的使用十分簡單,不需要其他的任何引數。下面繼續上一篇文章的例子:

SQL> SELECT /*+ NO_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
         1
         0
                   10
                    9

已選擇11行。


執行計劃
----------------------------------------------------------
Plan hash value: 2841162349

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    18 |   468 |    17   (6)| 00:00:01 |
|   1 |  VIEW                |      |    18 |   468 |    17   (6)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      |     9 |   234 |     9  (12)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |     9 |   117 |     4   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     9 |   117 |     4   (0)| 00:00:01 |
|*  6 |    HASH JOIN ANTI    |      |     9 |   234 |     9  (12)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| T2   |     9 |   117 |     4   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| T1   |     9 |   117 |     4   (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 (level=2)


統計資訊
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         61  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

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

如果兩個HINT同時存在,則Oracle選擇NO_NATIVE_FULL_OUTER_JOIN提示生效,而忽略NATIVE_FULL_OUTER_JOIN

SQL> SELECT /*+ NATIVE_FULL_OUTER_JOIN NO_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
         1
         0
                   10
                    9

已選擇11行。


執行計劃
----------------------------------------------------------
Plan hash value: 2841162349

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    18 |   468 |    17   (6)| 00:00:01 |
|   1 |  VIEW                |      |    18 |   468 |    17   (6)| 00:00:01 |
|   2 |   UNION-ALL          |      |       |       |            |          |
|*  3 |    HASH JOIN OUTER   |      |     9 |   234 |     9  (12)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T1   |     9 |   117 |     4   (0)| 00:00:01 |
|   5 |     TABLE ACCESS FULL| T2   |     9 |   117 |     4   (0)| 00:00:01 |
|*  6 |    HASH JOIN ANTI    |      |     9 |   234 |     9  (12)| 00:00:01 |
|   7 |     TABLE ACCESS FULL| T2   |     9 |   117 |     4   (0)| 00:00:01 |
|   8 |     TABLE ACCESS FULL| T1   |     9 |   117 |     4   (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 (level=2)


統計資訊
----------------------------------------------------------
         13  recursive calls
          0  db block gets
         61  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

 

 

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

相關文章