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")