本文共 4846 字，大约阅读时间需要 16 分钟。
在11.2中，Oracle对于全外连接的执行计划进行了优化。
这篇介绍新增的两个相关的HINT。
11gr2全外连接优化执行计划：
Oracle在推出了新的执行计划的同时，还提供了两个控制这个执行计划的提示NATIVE_FULL_OUTER_JOIN和NO_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
转载地址：http://xfmxo.baihongyu.com/