11gr2全外连接优化执行计划（三）

原创

小麦苗123 2021-04-09 14:44:57 ©著作权

文章标签 11gr2 文章分类 Oracle 数据库

©著作权归作者所有：来自51CTO博客作者小麦苗123的原创作品，请联系作者获取转载授权，否则将追究法律责任

虽然上一篇介绍了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;

已选择11行。

执行计划
----------------------------------------------------------
Plan hash value: 53297166

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提示后，执行计划变为新的外连接。