HINT no_swap_join_inputs/swap_join_inputs
10G以後
hash_join可以透過no_swap_join_inputs/swap_join_inputs來強制控制build表，配合leading或者ordered可以控制多表之前的連線順序。
>select
2 /*+
3 ordered
4 use_hash(t2)
5 use_hash(t3)
6 swap_join_inputs(t3)
7 use_hash(t4)
8 no_swap_join_inputs(t4)
9 */
10 * from t1,t2,t3,t4
11 where t1.object_id=t2.object_id
12 and t2.object_name=t3.object_name
13 and t3.owner=t4.owner
14 and t4.owner='MYDB'
15 /
已用時間: 00: 00: 00.07
執行計劃
----------------------------------------------------------
Plan hash value: 3494725078
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2137 | 801K| | 182 (2)| 00:00:03 |
|* 1 | HASH JOIN | | 2137 | 801K| | 182 (2)| 00:00:03 |
|* 2 | HASH JOIN | | 52 | 14976 | | 167 (2)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T3 | 40 | 3840 | | 15 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 11651 | 2184K| 1232K| 151 (1)| 00:00:02 |
| 5 | TABLE ACCESS FULL| T1 | 11651 | 1092K| | 15 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T2 | 11652 | 1092K| | 15 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | T4 | 41 | 3936 | | 15 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OWNER"="T4"."OWNER")
2 - access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
3 - filter("T3"."OWNER"='MYDB')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
7 - filter("T4"."OWNER"='MYDB')
Note
-----
- dynamic sampling used for this statement
說明：
hash_join可以透過no_swap_join_inputs/swap_join_inputs來強制控制build表，配合leading或者ordered可以控制多表之前的連線順序。
>select
2 /*+
3 ordered
4 use_hash(t2)
5 use_hash(t3)
6 swap_join_inputs(t3)
7 use_hash(t4)
8 no_swap_join_inputs(t4)
9 */
10 * from t1,t2,t3,t4
11 where t1.object_id=t2.object_id
12 and t2.object_name=t3.object_name
13 and t3.owner=t4.owner
14 and t4.owner='MYDB'
15 /
已用時間: 00: 00: 00.07
執行計劃
----------------------------------------------------------
Plan hash value: 3494725078
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2137 | 801K| | 182 (2)| 00:00:03 |
|* 1 | HASH JOIN | | 2137 | 801K| | 182 (2)| 00:00:03 |
|* 2 | HASH JOIN | | 52 | 14976 | | 167 (2)| 00:00:03 |
|* 3 | TABLE ACCESS FULL | T3 | 40 | 3840 | | 15 (0)| 00:00:01 |
|* 4 | HASH JOIN | | 11651 | 2184K| 1232K| 151 (1)| 00:00:02 |
| 5 | TABLE ACCESS FULL| T1 | 11651 | 1092K| | 15 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL| T2 | 11652 | 1092K| | 15 (0)| 00:00:01 |
|* 7 | TABLE ACCESS FULL | T4 | 41 | 3936 | | 15 (0)| 00:00:01 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OWNER"="T4"."OWNER")
2 - access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
3 - filter("T3"."OWNER"='MYDB')
4 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
7 - filter("T4"."OWNER"='MYDB')
Note
-----
- dynamic sampling used for this statement
說明：
ordered表示依據from後面寫的表的順序來做聯結~
寫hints，分開些思路清晰~ ordered後 from t1 ,t2 ,t3 ,t4說明首先使用t1做驅動表來連線t2,如何連線呢？看後面的hint use_hash(t2)
代表連線t2的方式是hash_join;然後用use_hash(t3)表示連線t3的方式是hash-join，那麼誰作build表呢？看後面的swap_join_inputs(t3)代表t3作build表和t1-t2的結果集作連線....依此類推~
標準的hint就應該這麼寫~ use_hash(x,y,z)這樣的寫法是不規範，這樣只是說出了x,y,z地聯接方式
寫hints，分開些思路清晰~ ordered後 from t1 ,t2 ,t3 ,t4說明首先使用t1做驅動表來連線t2,如何連線呢？看後面的hint use_hash(t2)
代表連線t2的方式是hash_join;然後用use_hash(t3)表示連線t3的方式是hash-join，那麼誰作build表呢？看後面的swap_join_inputs(t3)代表t3作build表和t1-t2的結果集作連線....依此類推~
標準的hint就應該這麼寫~ use_hash(x,y,z)這樣的寫法是不規範，這樣只是說出了x,y,z地聯接方式
ordered 是陳舊的hints,leading是用來代替ordered的~ leading不要求sql的寫法(from後面的順序不要求)，直接可以在leading中定義連線順序~
leading和ordered不能一起使用，也沒必要一起使用
-----用leading 來強制訪問表的順序
select count(1) from (SELECT /*+ USE_HASH(GC,C,T) leading(gc,c,t) */
count(*)
FROM GROUP_CUSTOMER GC
inner join CUSTOMER C on (gc.CUSTID = C.CUSTID)
inner join CM_CU_GRPAUDIT_REC T on(T.ENTITYID = C.CUSTID)
WHERE GC.REGION = 534
AND T.REGION = 534
AND C.REGION = 534
AND C.COUNTRYID = :v_1);
Plan hash value: 3730416791
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:01:35.54 | 378K| 378K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:35.54 | 378K| 378K| | | |
|* 2 | HASH JOIN | | 1 | 10888 | 12755 |00:01:35.54 | 378K| 378K| 7364K| 3022K| 14M (0)|
| 3 | PARTITION RANGE SINGLE | | 1 | 10888 | 160K|00:00:00.40 | 3037 | 3031 | | | |
|* 4 | TABLE ACCESS FULL | CM_CU_GRPAUDIT_REC | 1 | 10888 | 160K|00:00:00.35 | 3037 | 3031 | | | |
|* 5 | HASH JOIN | | 1 | 32739 | 5482 |00:01:34.98 | 375K| 375K| 3493K| 1511K| 4536K (0)|
| 6 | PARTITION RANGE SINGLE| | 1 | 36864 | 72528 |00:00:00.24 | 578 | 565 | | | |
|* 7 | INDEX FAST FULL SCAN | PK_CM_CU_GROUP | 1 | 36864 | 72528 |00:00:00.21 | 578 | 565 | | | |
| 8 | PARTITION RANGE SINGLE| | 1 | 32739 | 297K|00:01:34.39 | 375K| 374K| | | |
|* 9 | TABLE ACCESS FULL | CUSTOMER | 1 | 32739 | 297K|00:01:34.29 | 375K| 374K| | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."ENTITYID"="C"."CUSTID")
4 - filter("T"."REGION"=534)
5 - access("GC"."CUSTID"="C"."CUSTID")
7 - filter("GC"."REGION"=534)
9 - filter(("C"."COUNTRYID"=:V_1 AND "C"."REGION"=534))
-----用NO_SWAP_JOIN_INPUT(t) 來強制T表為被驅動表。
SELECT /*+ USE_HASH(GC,C,T) NO_SWAP_JOIN_INPUTS(t) leading(gc,c,t) */
count(*)
FROM GROUP_CUSTOMER GC
inner join CUSTOMER C on (gc.CUSTID = C.CUSTID)
inner join CM_CU_GRPAUDIT_REC T on(T.ENTITYID = C.CUSTID)
WHERE GC.REGION = 534
AND T.REGION = 534
AND C.REGION = 534
AND C.COUNTRYID = :v_1;
Plan hash value: 3348825172
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:01:44.56 | 378K| 378K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:44.56 | 378K| 378K| | | |
|* 2 | HASH JOIN | | 1 | 10888 | 12755 |00:01:44.56 | 378K| 378K| 1037K| 1037K| 2627K (0)|
|* 3 | HASH JOIN | | 1 | 32739 | 5482 |00:01:43.39 | 375K| 375K| 3493K| 1511K| 4542K (0)|
| 4 | PARTITION RANGE SINGLE| | 1 | 36864 | 72528 |00:00:00.25 | 578 | 565 | | | |
|* 5 | INDEX FAST FULL SCAN | PK_CM_CU_GROUP | 1 | 36864 | 72528 |00:00:00.23 | 578 | 565 | | | |
| 6 | PARTITION RANGE SINGLE| | 1 | 32739 | 297K|00:01:42.80 | 375K| 374K| | | |
|* 7 | TABLE ACCESS FULL | CUSTOMER | 1 | 32739 | 297K|00:01:42.70 | 375K| 374K| | | |
| 8 | PARTITION RANGE SINGLE | | 1 | 10888 | 160K|00:00:01.04 | 3037 | 3031 | | | |
|* 9 | TABLE ACCESS FULL | CM_CU_GRPAUDIT_REC | 1 | 10888 | 160K|00:00:01.00 | 3037 | 3031 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."ENTITYID"="C"."CUSTID")
3 - access("GC"."CUSTID"="C"."CUSTID")
5 - filter("GC"."REGION"=534)
7 - filter(("C"."COUNTRYID"=:V_1 AND "C"."REGION"=534))
9 - filter("T"."REGION"=534)
35 rows selected.
-----用leading 來強制訪問表的順序
select count(1) from (SELECT /*+ USE_HASH(GC,C,T) leading(gc,c,t) */
count(*)
FROM GROUP_CUSTOMER GC
inner join CUSTOMER C on (gc.CUSTID = C.CUSTID)
inner join CM_CU_GRPAUDIT_REC T on(T.ENTITYID = C.CUSTID)
WHERE GC.REGION = 534
AND T.REGION = 534
AND C.REGION = 534
AND C.COUNTRYID = :v_1);
Plan hash value: 3730416791
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:01:35.54 | 378K| 378K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:35.54 | 378K| 378K| | | |
|* 2 | HASH JOIN | | 1 | 10888 | 12755 |00:01:35.54 | 378K| 378K| 7364K| 3022K| 14M (0)|
| 3 | PARTITION RANGE SINGLE | | 1 | 10888 | 160K|00:00:00.40 | 3037 | 3031 | | | |
|* 4 | TABLE ACCESS FULL | CM_CU_GRPAUDIT_REC | 1 | 10888 | 160K|00:00:00.35 | 3037 | 3031 | | | |
|* 5 | HASH JOIN | | 1 | 32739 | 5482 |00:01:34.98 | 375K| 375K| 3493K| 1511K| 4536K (0)|
| 6 | PARTITION RANGE SINGLE| | 1 | 36864 | 72528 |00:00:00.24 | 578 | 565 | | | |
|* 7 | INDEX FAST FULL SCAN | PK_CM_CU_GROUP | 1 | 36864 | 72528 |00:00:00.21 | 578 | 565 | | | |
| 8 | PARTITION RANGE SINGLE| | 1 | 32739 | 297K|00:01:34.39 | 375K| 374K| | | |
|* 9 | TABLE ACCESS FULL | CUSTOMER | 1 | 32739 | 297K|00:01:34.29 | 375K| 374K| | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."ENTITYID"="C"."CUSTID")
4 - filter("T"."REGION"=534)
5 - access("GC"."CUSTID"="C"."CUSTID")
7 - filter("GC"."REGION"=534)
9 - filter(("C"."COUNTRYID"=:V_1 AND "C"."REGION"=534))
-----用NO_SWAP_JOIN_INPUT(t) 來強制T表為被驅動表。
SELECT /*+ USE_HASH(GC,C,T) NO_SWAP_JOIN_INPUTS(t) leading(gc,c,t) */
count(*)
FROM GROUP_CUSTOMER GC
inner join CUSTOMER C on (gc.CUSTID = C.CUSTID)
inner join CM_CU_GRPAUDIT_REC T on(T.ENTITYID = C.CUSTID)
WHERE GC.REGION = 534
AND T.REGION = 534
AND C.REGION = 534
AND C.COUNTRYID = :v_1;
Plan hash value: 3348825172
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 1 |00:01:44.56 | 378K| 378K| | | |
| 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:01:44.56 | 378K| 378K| | | |
|* 2 | HASH JOIN | | 1 | 10888 | 12755 |00:01:44.56 | 378K| 378K| 1037K| 1037K| 2627K (0)|
|* 3 | HASH JOIN | | 1 | 32739 | 5482 |00:01:43.39 | 375K| 375K| 3493K| 1511K| 4542K (0)|
| 4 | PARTITION RANGE SINGLE| | 1 | 36864 | 72528 |00:00:00.25 | 578 | 565 | | | |
|* 5 | INDEX FAST FULL SCAN | PK_CM_CU_GROUP | 1 | 36864 | 72528 |00:00:00.23 | 578 | 565 | | | |
| 6 | PARTITION RANGE SINGLE| | 1 | 32739 | 297K|00:01:42.80 | 375K| 374K| | | |
|* 7 | TABLE ACCESS FULL | CUSTOMER | 1 | 32739 | 297K|00:01:42.70 | 375K| 374K| | | |
| 8 | PARTITION RANGE SINGLE | | 1 | 10888 | 160K|00:00:01.04 | 3037 | 3031 | | | |
|* 9 | TABLE ACCESS FULL | CM_CU_GRPAUDIT_REC | 1 | 10888 | 160K|00:00:01.00 | 3037 | 3031 | | | |
----------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T"."ENTITYID"="C"."CUSTID")
3 - access("GC"."CUSTID"="C"."CUSTID")
5 - filter("GC"."REGION"=534)
7 - filter(("C"."COUNTRYID"=:V_1 AND "C"."REGION"=534))
9 - filter("T"."REGION"=534)
35 rows selected.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29446986/viewspace-1260105/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- oracle hintOracle
- oracle hint之hint_index_ffs,index_joinOracleIndex
- ORACLE 部分HINTOracle
- Append HintAPP
- oracle hint (續)Oracle
- Oracle Hint 精華Oracle
- 【sql hint 提示】SQL
- sql hint articleSQL
- Oralce SQL hintSQL
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（五）Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（四）Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（三）Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（二）Oracle
- 【TUNE_ORACLE】Oracle Hint之常用Hint功能概述（一）Oracle
- 【HINT】使用“NO_INDEX ”Hint提示避免SQL使用特定索引IndexSQL索引
- zt_Oracle hint driving_site Hint的用法Oracle
- LOCAL INDEX和HINT的使用【DO BE USED LOCAL INDEX IN HINT】薦Index
- oracle之hint概述Oracle
- oracle常見hintOracle
- mysql常用的hintMySql
- MySQL SQL hint 提示MySql
- oracle hint簡述Oracle
- oracle hint (續-0)Oracle
- oracle hint_no_indexOracleIndex
- Oracle Hint 精華文章Oracle
- 常用的Oracle HintOracle
- Oracle Hint 詳解Oracle
- Oracle Hint的用法Oracle
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- ORACLE的HINT詳解Oracle
- SAP HANA Hint簡介
- Hint學習筆記筆記
- Oracle中Hint隨記Oracle
- oracle hint no_mergeOracle
- oracle hint_no_expand_no_factOracle
- oracle hint_cache_nocacheOracle
- Oracle 常用HINT介紹Oracle
- （轉）Oracle Hint的用法Oracle