ITPub博客

首页 > Linux操作系统 > Linux操作系统 > hash join and hash join semi

hash join and hash join semi

原创 Linux操作系统 作者:shuangoracle 时间:2012-05-27 22:08:14 0 删除 编辑
测试环境:
SQL> select * from t1;

        ID NAME
---------- --------------------
         1 A
         2 B
         3
         4 D

SQL> select * from t2;

        ID NAME
---------- --------------------
         1 A
         1 E

SQL> set autot on exp
--使用in
SQL> select id,name from t1 where t1.id in (select t2.id from t2);

        ID NAME
---------- --------------------
         1 A


Execution Plan
----------------------------------------------------------
Plan hash value: 1713220790

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    16 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |     1 |    16 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T1   |     4 |    12 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T2   |     2 |    26 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="T2"."ID")

Note
-----
   - dynamic sampling used for this statement

--两个表等值连接
SQL> select t1.id,t1.name from t1,(select id from t2) t2 where t1.id=t2.id;

        ID NAME
---------- --------------------
         1 A
         1 A


Execution Plan
----------------------------------------------------------
Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     2 |    32 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     2 |    32 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |     2 |    26 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |     4 |    12 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."ID"="ID")

Note
-----
   - dynamic sampling used for this statement
   
in括号里子查询会走join-semi,等值连接会走hash join;in括号内的子查询有虑重功能,相当于子查询中有distinct。

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

请登录后发表评论 登录
全部评论

注册时间:2010-08-25

  • 博文量
    84
  • 访问量
    213836