swapping join input
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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- Heavy swappingAPP
- join、inner join、left join、right join、outer join的區別
- MySQL LEFT JOIN/ INNER JOIN/RIGHT JOINMySql
- sql中的join、left join、right joinSQL
- Mysql join 的本質，Nested Loop Join 和 Hash JoinMySqlOOP
- mysql left join轉inner joinMySql
- $(":input")和$("input")區別
- .join()
- sql之left join、right join、inner join的區別SQL
- 連線查詢簡析 join 、 left join 、 right join
- hash join\nest loop join\sort merge join的實驗OOP
- WARNING: Heavy swapping observed on system in last 5 minsAPPAST
- ZT：簡單介紹join,outer-join,semi-join,anti-join的區別
- LEFT JOIN 和JOIN 多表連線
- HASH JOIN ,MERGE JOIN ,NESTED LOOPOOP
- sql：left join和join區別SQL
- SQL中聯表查詢操作（LEFT JOIN, RIGHT JOIN, INNER JOIN）SQL
- delete input 與 delete all inputdelete
- WARNING: Heavy swapping observed on system in last 5 mins.APPAST
- input
- 數倉工具—Hive語法之map join、reduce join、smb join(8)Hive
- sql的left join 、right join 、inner join之間的區別SQL
- mysql中的left join、right join 、inner join的詳細用法MySql
- nested loop,sort merge join,hash joinOOP
- Python -- raw_input() and input() -- ACMPythonACM
- JavaScript join()JavaScript
- FILTER JOINFilter
- SQL joinSQL
- hadoop 多表join：Map side join及Reduce side join範例HadoopIDE
- left join,right join,inner join的條件on和where的區別
- HINT no_swap_join_inputs/swap_join_inputs
- sql left join 和 right join解釋SQL
- Oracle 12.2 Heavy swapping 資料庫自動關閉OracleAPP資料庫
- TECH: Unix Virtual Memory, Paging & Swapping explained (Doc ID 17094.1)APPAI
- jquery 中$("form :input") $("form input") 區別jQueryORM
- raw_input() 與 input()的區別
- jQuery :inputjQuery
- <input> disabled