ITPub博客

首页 > Linux操作系统 > Linux操作系统 > swapping join input

swapping join input

原创 Linux操作系统 作者:wei-xh 时间:2011-07-28 10:32:13 0 删除 编辑

select * from wxh_tbd1 a ,wxh_tbd2 b where a.id=b.id(+);
对于如上查询,在10G以前,执行计划的结果只可能为(我们只考虑hash join):
-------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |     1 |   128 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |          |     1 |   128 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| WXH_TBD1 |     1 |    13 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| WXH_TBD2 |     1 |   115 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------
这样有一个问题,如果wxh_tbd1非常大,而wxh_tbd2非常小,那么我们可能想wxh_tbd2作为build table,这样效率会更高
ORACLE10G后,会根据表大小来自动的完成这种切换
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |   128 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|          |     1 |   128 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | WXH_TBD2 |     1 |   115 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | WXH_TBD1 |     1 |    13 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------
执行计划显示了一种新的join方式 HASH JOIN RIGHT OUTER

如果我们想改变这种join的顺序,可以通过hint swap_join_inputs来达到目的
select /*+ swap_join_inputs(b) */* from wxh_tbd1 a ,wxh_tbd2 b where a.id=b.id(+);
----------------------------------------------------------------------------------
| Id  | Operation             | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |          |     1 |   128 |     9  (12)| 00:00:01 |
|*  1 |  HASH JOIN RIGHT OUTER|          |     1 |   128 |     9  (12)| 00:00:01 |
|   2 |   TABLE ACCESS FULL   | WXH_TBD2 |     1 |   115 |     4   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL   | WXH_TBD1 |     1 |    13 |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------
 select * from wxh_tbd1 a  where a.object_id in ( select object_id from wxh_tbd2 b);
 ---------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          |   398K|    37M|  3640   (1)| 00:00:44 |
|*  1 |  HASH JOIN RIGHT SEMI|          |   398K|    37M|  3640   (1)| 00:00:44 |
|   2 |   TABLE ACCESS FULL  | WXH_TBD2 | 49838 |   243K|   414   (1)| 00:00:05 |
|   3 |   TABLE ACCESS FULL  | WXH_TBD1 |   398K|    35M|  3222   (1)| 00:00:39 |
---------------------------------------------------------------------------------

select /*+ leading(a) */* from wxh_tbd1 a  where a.object_id in ( select object_id from wxh_tbd2 b);
---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          |   398K|    37M|       |  5663   (1)| 00:01:08 |
|*  1 |  HASH JOIN SEMI    |          |   398K|    37M|    39M|  5663   (1)| 00:01:08 |
|   2 |   TABLE ACCESS FULL| WXH_TBD1 |   398K|    35M|       |  3222   (1)| 00:00:39 |
|   3 |   TABLE ACCESS FULL| WXH_TBD2 | 49838 |   243K|       |   414   (1)| 00:00:05 |
---------------------------------------------------------------------------------------
 select * from wxh_tbd2 a where a.object_id in ( select object_id from wxh_tbd1 b);
 ---------------------------------------------------------------------------------------
| Id  | Operation          | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |          | 49663 |  4752K|       |  4208   (1)| 00:00:51 |
|*  1 |  HASH JOIN SEMI    |          | 49663 |  4752K|  5112K|  4208   (1)| 00:00:51 |
|   2 |   TABLE ACCESS FULL| WXH_TBD2 | 49838 |  4526K|       |   415   (1)| 00:00:05 |
|   3 |   TABLE ACCESS FULL| WXH_TBD1 |   398K|  1946K|       |  3215   (1)| 00:00:39 |
---------------------------------------------------------------------------------------

select * from wxh_tbd2 a where a.object_id in ( select /*+ swap_join_inputs(b) */ object_id from wxh_tbd1 b);
-----------------------------------------------------------------------------------------
| Id  | Operation            | Name     | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |          | 49663 |  4752K|       |  4207   (1)| 00:00:51 |
|*  1 |  HASH JOIN RIGHT SEMI|          | 49663 |  4752K|  6624K|  4207   (1)| 00:00:51 |
|   2 |   TABLE ACCESS FULL  | WXH_TBD1 |   398K|  1946K|       |  3215   (1)| 00:00:39 |
|   3 |   TABLE ACCESS FULL  | WXH_TBD2 | 49838 |  4526K|       |   415   (1)| 00:00:05 |
-----------------------------------------------------------------------------------------

swap_join_inputs还可以控制hash join的顺序,t1作为build表和T2做hash_join,然后t3作为build表和t1,t2的结果集作hash_join,在把t3,t1,t2的结果集作build表和t4做hash_join
create table t1 as select * from dba_objects;
create table t2 as select * from dba_objects;
create table t3 as select * from dba_objects;
create table t4 as select * from dba_objects;
exec dbms_stats.gather_table_stats(user,'t1');
exec dbms_stats.gather_table_stats(user,'t2');
exec dbms_stats.gather_table_stats(user,'t3');
exec dbms_stats.gather_table_stats(user,'t4');  

select
/*+                            
 ordered                        
 use_hash(t2)                   
 use_hash(t3)                   
 swap_join_inputs(t3)           
 use_hash(t4)                   
 no_swap_join_inputs(t4)        
 */
 *
  from t1, t2, t3, t4
 where t1.object_id = t2.object_id
   and t2.object_name = t3.object_name
   and t3.owner = t4.owner
   and t4.owner = 'MYDB' ;

-------------------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     9 |  3348 |       |   431   (1)| 00:00:06 |
|*  1 |  HASH JOIN           |      |     9 |  3348 |       |   431   (1)| 00:00:06 |
|*  2 |   HASH JOIN          |      |     6 |  1674 |       |   373   (1)| 00:00:05 |
|*  3 |    TABLE ACCESS FULL | T3   |     2 |   186 |       |    57   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |      | 19854 |  3606K|  2048K|   315   (1)| 00:00:04 |
|   5 |     TABLE ACCESS FULL| T1   | 19948 |  1811K|       |    58   (2)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   | 19949 |  1811K|       |    58   (2)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL  | T4   |     2 |   186 |       |    57   (0)| 00:00:01 |
------------------------------------------------------------------------------------- 

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

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2340996