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博客 ” ，链接：https://blog.itpub.net/29446986/viewspace-1260105/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
