通过swap_join_inputs来控制多表连接hash join中的build table

hash_join可以通过no_swap_join_inputs/swap_join_inputs来强制控制build表，配合leading或者ordered可以控制多表之前的连接顺序
----------------创建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');  
-----------------控制hash join的顺序，先t2,t3做jion，t1跟t2,t3的结果做join，最后t4再跟上面的结果做join
select
/*+                            
 ordered                        
 use_hash(t3)                   
 use_hash(t1)                   
 swap_join_inputs(t1)           
 use_hash(t4)                   
 swap_join_inputs(t4)        
 */
 *
  from t2, t3,t1, 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     |      |   333K|   119M|       |   467   (2)| 00:00:06 |
|*  1 |  HASH JOIN           |      |   333K|   119M|       |   467   (2)| 00:00:06 |
|*  2 |   TABLE ACCESS FULL  | T4   |   493 | 46342 |       |    62   (0)| 00:00:01 |
|*  3 |   HASH JOIN          |      |   676 |   186K|  2096K|   402   (1)| 00:00:05 |
|   4 |    TABLE ACCESS FULL | T1   | 20193 |  1853K|       |    63   (2)| 00:00:01 |
|*  5 |    HASH JOIN         |      |   679 |   124K|  2096K|   231   (1)| 00:00:03 |
|   6 |     TABLE ACCESS FULL| T2   | 20194 |  1853K|       |    63   (2)| 00:00:01 |
|*  7 |     TABLE ACCESS FULL| T3   |   493 | 46342 |       |    62   (0)| 00:00:01 |
-------------------------------------------------------------------------------------   
  
-----------------控制hash join的顺序，t1跟t2做join，t3再与t1,t2的结果做join，以上的结果再与t4做join
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     |      |   333K|   119M|       |   458   (2)| 00:00:06 |
|*  1 |  HASH JOIN           |      |   333K|   119M|       |   458   (2)| 00:00:06 |
|*  2 |   HASH JOIN          |      |   676 |   186K|       |   393   (1)| 00:00:05 |
|*  3 |    TABLE ACCESS FULL | T3   |   493 | 46342 |       |    62   (0)| 00:00:01 |
|*  4 |    HASH JOIN         |      | 20117 |  3693K|  2096K|   330   (1)| 00:00:04 |
|   5 |     TABLE ACCESS FULL| T1   | 20193 |  1853K|       |    63   (2)| 00:00:01 |
|   6 |     TABLE ACCESS FULL| T2   | 20194 |  1853K|       |    63   (2)| 00:00:01 |
|*  7 |   TABLE ACCESS FULL  | T4   |   493 | 46342 |       |    62   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

select * from wxh_tbd1 a ,wxh_tbd2 b where a.id=b.id(+);
对于如上查询，在10G以前，执行计划的结果只可能为WXH_TBD1为build表(我们只考虑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 |
-------------------------------------------------------------------------------

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 |
----------------------------------------------------------------------------------

无论如何，如果统计信息错误等情况，可能cbo意识不到需要调换build表，那么我们可以通过hint swap_join_inputs来达到目的。
好处是显而易见的，选择小的build表能提高效率
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 |
----------------------------------------------------------------------------------

对于子查询in ,exists操作依然有效，不一一列举
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 |
-----------------------------------------------------------------------------------------
  
 

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

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