hash_join可以通过swap_join_inputs来强制指定build表(或者驱动表),no_swap_join_inputs来强制
指定prob表(探查表,或者说被驱动表)
配合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;
/*
Leading 控制表的关联顺序为T2,T3,T1,T4.先t2,t3做jion,USE_HASH指定表的连接方式,
然后结果集与T1进行关联,通过SWAP_JOIN_INPUTS来指定T1表作为(T2,T3 Hash Join结果集)关联的驱动表。
然后结果集与T4进行关联,通过NO_SWAP_JOIN_INPUTS来指定T4作为探查表(或者说被驱动表)
*/
SELECT
/*+
Leading(T2,T3,T1,T4)
USE_HASH(T3)
USE_HASH(T1)
SWAP_JOIN_INPUTS(T1)
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';
执行计划
----------------------------------------------------------
Plan hash value: 3177457315
-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2284 | 1846K| | 1616 (1)| 00:00:20 |
|* 1 | HASH JOIN | | 2284 | 1846K| | 1616 (1)| 00:00:20 |
|* 2 | HASH JOIN | | 299 | 181K| 7656K| 1424 (1)| 00:00:18 |
| 3 | TABLE ACCESS FULL | T1 | 35780 | 7232K| | 192 (2)| 00:00:03 |
|* 4 | HASH JOIN | | 375 | 151K| 9584K| 851 (1)| 00:00:11 |
| 5 | TABLE ACCESS FULL| T2 | 44802 | 9056K| | 193 (2)| 00:00:03 |
|* 6 | TABLE ACCESS FULL| T3 | 8 | 1656 | | 191 (1)| 00:00:03 |
|* 7 | TABLE ACCESS FULL | T4 | 8 | 1656 | | 192 (2)| 00:00:03 |
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T3"."OWNER"="T4"."OWNER")
2 - access("T1"."OBJECT_ID"="T2"."OBJECT_ID")
4 - access("T2"."OBJECT_NAME"="T3"."OBJECT_NAME")
6 - filter("T3"."OWNER"='MYDB')
7 - filter("T4"."OWNER"='MYDB')