ITPub博客

首页 > 数据库 > Oracle > Native Full Outer Join

Native Full Outer Join

Oracle 作者:lhrbest 时间:2015-05-18 11:10:38 2 删除 编辑
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")

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26736162/viewspace-1660038/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
QQ:646634621| 网名:小麦苗| 微信公众号:xiaomaimiaolhr| 11g OCM| QQ群:618766405 微信群:私聊| 《数据库笔试面试宝典》作者| OCP、OCM、高可用(RAC+DG+OGG)网络班开讲啦,有需要的小伙伴可以私聊我。

注册时间:2012-09-23

  • 博文量
    1367
  • 访问量
    8348719