通三个提示来控制HASH_JOIN顺序和内建与探测表

转自http://blog.csdn.net/zengmuansha/article/details/7448723

hash_join可以通过no_swap_join_inputs/swap_join_inputs来强制控制build表，配合leading可以控制多表之前的连接顺序
----------------创建4个测试表
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');
 
1 USE_HASH 说明采用什么连接方法

2 LEADING 说明连接顺序

3 SWAP_JOIN_INPUTS 说明 连接当中谁做内建表

4 NO_SWAP_JOIN_INPUTS 说它做探测表

select
*+   LEADING(t3,t4,t2,t1)  USE_HASH(T1,T2,T3,t4)  swap_join_inputs(t4) no_swap_join_inputs(t2) no_swap_join_inputs(t1) */
*
 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 = 'SYS' ;

SELECT STATEMENT, GOAL = ALL_ROWS Depth=0  Operation=SELECT STATEMENT Cost=1230971
 HASH JOIN Depth=1  Operation=HASH JOIN Cost=1230971
  HASH JOIN Depth=2  Operation=HASH JOIN Cost=54317
   HASH JOIN Depth=3  Operation=HASH JOIN Cost=780
    TABLE ACCESS FULL Depth=4 Object name=T4 Operation=TABLE ACCESS Cost=374
    TABLE ACCESS FULL Depth=4 Object name=T3 Operation=TABLE ACCESS Cost=374
   TABLE ACCESS FULL Depth=3 Object name=T2 Operation=TABLE ACCESS Cost=375
  TABLE ACCESS FULL Depth=2 Object name=T1 Operation=TABLE ACCESS Cost=375

 

唯一可惜的是 SWAP_JOIN_INPUTS不怎么支持 （t3,t4,t1)写法