hash join right outer小记

10G以后，对于HASH外连接，ORACLE会智能的选择驱动表。

select  count(*) from wxh_tbd1 a ,wxh_tbd2 b where a.object_id=b.object_id(+);

对于上面的查询，如果选择以A为驱动表，那么执行计划的连接操作显示为HASH JOIN OUTER 。如果以B为驱动表执行计划的连接操作显示为HASH JOIN RIGHE OUTER .

select * from v$version;

BANNER
---------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE    11.1.0.7.0      Production
TNS for Linux: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

create table wxh_tbd1 as select * from dba_objects;

Table created.

create table wxh_tbd2 as select * from all_objects;

Table created.

select count(*) from wxh_tbd1 a ,wxh_tbd2 b where a.object_id=b.object_id(+);

  COUNT(*)
----------
     18440

Execution Plan
----------------------------------------------------------
Plan hash value: 4164641127

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    10 |   146   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE        |          |     1 |    10 |            |          |
|*  2 |   HASH JOIN RIGHT OUTER|          | 18440 |   180K|   146   (1)| 00:00:02 |
|   3 |    TABLE ACCESS FULL   | WXH_TBD2 | 18102 | 90510 |    72   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | WXH_TBD1 | 18440 | 92200 |    73   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

select /*+ leading(a) */count(*) from wxh_tbd1 a ,wxh_tbd2 b where a.object_id=b.object_id(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 1682470686

--------------------------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    10 |   146   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE     |          |     1 |    10 |            |          |
|*  2 |   HASH JOIN OUTER   |          | 18440 |   180K|   146   (1)| 00:00:02 |
|   3 |    TABLE ACCESS FULL| WXH_TBD1 | 18440 | 92200 |    73   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| WXH_TBD2 | 18102 | 90510 |    72   (0)| 00:00:01 |
--------------------------------------------------------------------------------
SELECT value FROM v$parameter_valid_values WHERE name = 'optimizer_features_enable';

VALUE
------------------------------
8.0.0
8.0.3
8.0.4
8.0.5
8.0.6
8.0.7
8.1.0
8.1.3
8.1.4
8.1.5
8.1.6
8.1.7
9.0.0
9.0.1
9.2.0
10.1.0
10.1.0.3
10.1.0.4
10.1.0.5
10.2.0.1
10.2.0.2
10.2.0.3
10.2.0.4
10.2.0.5
11.1.0.6
11.1.0.7
11.1.0.7.1

select /*+ optimizer_features_enable('9.2.0') */count(*) from wxh_tbd1 a ,wxh_tbd2 b where a.object_id=b.object_id(+);

Execution Plan
----------------------------------------------------------
Plan hash value: 1682470686

----------------------------------------------------------------
| Id  | Operation           | Name     | Rows  | Bytes | Cost  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |     1 |    10 |    87 |
|   1 |  SORT AGGREGATE     |          |     1 |    10 |       |
|*  2 |   HASH JOIN OUTER   |          | 18440 |   180K|    87 |
|   3 |    TABLE ACCESS FULL| WXH_TBD1 | 18440 | 92200 |    42 |
|   4 |    TABLE ACCESS FULL| WXH_TBD2 | 18102 | 90510 |    41 |
----------------------------------------------------------------

select /*+ optimizer_features_enable('10.1.0') */count(*) from wxh_tbd1 a ,wxh_tbd2 b where a.object_id=b.object_id(+);

 

Execution Plan
----------------------------------------------------------
Plan hash value: 4164641127

-----------------------------------------------------------------------------------
| Id  | Operation              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |          |     1 |    10 |   146   (1)| 00:00:02 |
|   1 |  SORT AGGREGATE        |          |     1 |    10 |            |          |
|*  2 |   HASH JOIN RIGHT OUTER|          | 18440 |   180K|   146   (1)| 00:00:02 |
|   3 |    TABLE ACCESS FULL   | WXH_TBD2 | 18102 | 90510 |    72   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL   | WXH_TBD1 | 18440 | 92200 |    73   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

转载于:http://blog.itpub.net/22034023/viewspace-682379/