Native Full Outer Join

Native Full Outer Join是11g的特性，采用此特性，FULL JOIN时只需要对基表做一次扫描。而在Oracle 10g中，缺省FULL JOIN（等同于：FULL OUTER JOIN）时需要对基表做两次扫描，理论上来说性能提升了一倍。实际上，在10.2.0.3以上版本中也可以使用Native Full Outer Join，但缺省不使用此特性，除非采用以下方式：
1)使用HINT：NATIVE_FULL_OUTER_JOIN
2)将参数“_optimizer_native_full_outer_join”改成force
    ALTER SESSION SET "_optimizer_native_full_outer_join" = force;
    在Oracle的官方文档中，建议从10.2.0.4版本开始，直接从system级别设置此参数为force。
实验：
SELECT COUNT(1) FROM test_pk02 a full join test_pk03 b on a.object_id = b.object_id;
版本10.2.0.5的执行计划：
-------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |       |       |       |   876 (100)|          |
|   1 |  SORT AGGREGATE       |           |     1 |       |       |            |          |
|   2 |   VIEW                |           |   108K|       |       |   876   (2)| 00:00:11 |
|   3 |    UNION-ALL          |           |       |       |       |            |          |
|*  4 |     HASH JOIN OUTER   |           |   107K|  1051K|  1792K|   438   (2)| 00:00:06 |
|   5 |      TABLE ACCESS FULL| TEST_PK02 |   107K|   525K|       |   131   (2)| 00:00:02 |
|   6 |      TABLE ACCESS FULL| TEST_PK03 |   107K|   525K|       |   131   (2)| 00:00:02 |
|*  7 |     HASH JOIN ANTI    |           |   471 |  4710 |  1792K|   438   (2)| 00:00:06 |
|   8 |      TABLE ACCESS FULL| TEST_PK03 |   107K|   525K|       |   131   (2)| 00:00:02 |
|   9 |      TABLE ACCESS FULL| TEST_PK02 |   107K|   525K|       |   131   (2)| 00:00:02 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
   7 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
版本11.2.0.4的执行计划：
------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |       |       |   172 (100)|          |
|   1 |  SORT AGGREGATE        |           |     1 |       |            |          |
|   2 |   VIEW                 | VW_FOJ_0  | 50598 |       |   172   (2)| 00:00:03 |
|*  3 |    HASH JOIN FULL OUTER|           | 50598 |   494K|   172   (2)| 00:00:03 |
|   4 |     TABLE ACCESS FULL  | TEST_PK02 | 50598 |   247K|    86   (2)| 00:00:02 |
|   5 |     TABLE ACCESS FULL  | TEST_PK03 | 50598 |   247K|    86   (2)| 00:00:02 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."OBJECT_ID"="B"."OBJECT_ID")
在10.2.0.5中使用Hint：
SELECT /*+ NATIVE_FULL_OUTER_JOIN test34 */ COUNT(1) FROM test_pk02 a full  join test_pk03 b on a.object_id = b.object_id;
--------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |       |       |       |   438 (100)|          |
|   1 |  SORT AGGREGATE        |           |     1 |       |       |            |          |
|   2 |   VIEW                 | VW_FOJ_0  |   107K|       |       |   438   (2)| 00:00:06 |
|*  3 |    HASH JOIN FULL OUTER|           |   107K|  1051K|  1792K|   438   (2)| 00:00:06 |
|   4 |     TABLE ACCESS FULL  | TEST_PK03 |   107K|   525K|       |   131   (2)| 00:00:02 |
|   5 |     TABLE ACCESS FULL  | TEST_PK02 |   107K|   525K|       |   131   (2)| 00:00:02 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("A"."OBJECT_ID"="B"."OBJECT_ID")