通過swap_join_inputs來控制多表連線hash join中的build table

wei-xh發表於2011-08-04
UI

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/，如需轉載，請註明出處，否則將追究法律責任。

相關文章