swap_join_inputs是针对哈希连接的hint，它的含义是让优化器交换原哈希连接的驱动表和被驱动表的顺序，即在依然走哈希连接的情况下让原哈希连接的驱动表变被驱动表，让原哈希连接的被驱动表变为驱动表。

注意，在swap_join_inputs hint中指定的目标表应该是原哈希连接中的被驱动表，否则oracle会忽略该hint。

/*+ swap_join_inputs(原哈希连接的被驱动表) */

其使用范例如下：

select/*+leading(dept)use_hash(emp)swap_join_intputs(emp)*/*fromemp,deptwhereemp.deptno=dept.deptno

测试案例：

SCOTT@ORA12C>createtablet1asselect*fromdba_objectswhererownum<2;Tablecreated.SCOTT@ORA12C>createtablet2asselect*fromdba_objectswhererownum<12;Tablecreated.SCOTT@ORA12C>createtablet3asselect*fromdba_objectswhererownum<22;Tablecreated.

收集统计信息：

SCOTT@ORA12C>execdbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T1',estimate_percent=>100,cascade=>true,method_opt=>'forallcolumnssize1',no_invalidate=>false);PL/SQLproceduresuccessfullycompleted.SCOTT@ORA12C>execdbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T2',estimate_percent=>100,cascade=>true,method_opt=>'forallcolumnssize1',no_invalidate=>false);PL/SQLproceduresuccessfullycompleted.SCOTT@ORA12C>execdbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'T3',estimate_percent=>100,cascade=>true,method_opt=>'forallcolumnssize1',no_invalidate=>false);PL/SQLproceduresuccessfullycompleted.

3个表的记录如下：

SCOTT@ORA12C>selectcount(*)fromt1;COUNT(*)-----------------11rowselected.SCOTT@ORA12C>selectcount(*)fromt2;COUNT(*)-----------------111rowselected.SCOTT@ORA12C>selectcount(*)fromt3;COUNT(*)-----------------211rowselected.

现在我们来让表T2和T3做哈希连接，由于T3表的记录数比T2表的记录数多，所以这里指定T3为哈希连接的被驱动表：

select/*+ordereduse_hash(t3)*/t2.object_name,t3.object_type2fromt2,t3wheret2.object_id=t3.object_id;ExecutionPlan----------------------------------------------------------Planhashvalue:1730954469---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||11|220|6(0)|00:00:01||*1|HASHJOIN||11|220|6(0)|00:00:01||2|TABLEACCESSFULL|T2|11|110|3(0)|00:00:01||3|TABLEACCESSFULL|T3|21|210|3(0)|00:00:01|---------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

可以看到，上述SQL的执行计划现在走的是哈希连接，并且被驱动表示表T3.

如果我们想让哈希连接的被驱动表由T3变成T2，可以在上述sql加入swap_join_inputs hint：

select/*+ordereduse_hash(t3)swap_join_inputs(t3)*/t2.object_name,t3.object_type2fromt2,t3wheret2.object_id=t3.object_id;ExecutionPlan----------------------------------------------------------Planhashvalue:1723280936---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||11|220|6(0)|00:00:01||*1|HASHJOIN||11|220|6(0)|00:00:01||2|TABLEACCESSFULL|T3|21|210|3(0)|00:00:01||3|TABLEACCESSFULL|T2|11|110|3(0)|00:00:01|---------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

用leading(t3) use_hash(t2)也可以同样达到目的：

select/*+leading(t3)use_hash(t2)*/t2.object_name,t3.object_type2fromt2,t3wheret2.object_id=t3.object_id;ExecutionPlan----------------------------------------------------------Planhashvalue:1723280936---------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|---------------------------------------------------------------------------|0|SELECTSTATEMENT||11|220|6(0)|00:00:01||*1|HASHJOIN||11|220|6(0)|00:00:01||2|TABLEACCESSFULL|T3|21|210|3(0)|00:00:01||3|TABLEACCESSFULL|T2|11|110|3(0)|00:00:01|---------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("T2"."OBJECT_ID"="T3"."OBJECT_ID")

由此可见在两个表关联的时候，可以用其他hint代替swap_join_inputs来达到相同的目的：

那么多表关联呢：

select/*+ordereduse_hash(t3)*/t1.owner,t2.object_name,t3.object_type2fromt2,t3,t1wheret2.object_id=t3.object_idandt1.object_type=t3.object_type;ExecutionPlan----------------------------------------------------------Planhashvalue:98820498----------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|9(0)|00:00:01||*1|HASHJOIN||4|120|9(0)|00:00:01||*2|HASHJOIN||11|220|6(0)|00:00:01||3|TABLEACCESSFULL|T2|11|110|3(0)|00:00:01||4|TABLEACCESSFULL|T3|21|210|3(0)|00:00:01||5|TABLEACCESSFULL|T1|1|10|3(0)|00:00:01|----------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("T1"."OBJECT_TYPE"="T3"."OBJECT_TYPE")2-access("T2"."OBJECT_ID"="T3"."OBJECT_ID"

可以看到，现在上述sql的执行计划是先由表T2和表T3做哈希连接，然后将他们做哈希连接的连接结果集再和表T1做一次哈希连接。

表T1的记录数为1，表T2的记录数为11，表T3的记录数为21，所以当表的T2和T3做哈希连接时，记录数多的表T3应该是被驱动表，这是因为我们在上述sql中使用了ordered hint和use_hash HINT指定表T3作为表T2和T3连接的时的被驱动表，所以oracle这里选择了表T2和T3做哈希连接，并且选择了表T3作为该哈希连接的被驱动表，这是没有问题的，现在问题在于表T1的记录数仅为1，所以当表T2和T3做哈希连接的结果再和表T1做哈希连接时，表T1应该是驱动表，而不是在上述执行计划里显示的那样作为第二个哈希连接的被驱动表。

使用下面HINT:

select/*+ordereduse_hash(t3)*/t1.owner,t2.object_name,t3.object_type2fromt1,t2,t3wheret2.object_id=t3.object_idandt1.object_type=t3.object_type;ExecutionPlan----------------------------------------------------------Planhashvalue:38266800------------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|------------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|9(0)|00:00:01||*1|HASHJOIN||4|120|9(0)|00:00:01||2|MERGEJOINCARTESIAN||11|220|6(0)|00:00:01||3|TABLEACCESSFULL|T1|1|10|3(0)|00:00:01||4|BUFFERSORT||11|110|3(0)|00:00:01||5|TABLEACCESSFULL|T2|11|110|3(0)|00:00:01||6|TABLEACCESSFULL|T3|21|210|3(0)|00:00:01|------------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("T2"."OBJECT_ID"="T3"."OBJECT_ID"AND"T1"."OBJECT_TYPE"="T3"."OBJECT_TYPE")

select/*+leading(t1)use_hash(t3)*/t1.owner,t2.object_name,t3.object_type2fromt1,t2,t3wheret2.object_id=t3.object_idandt1.object_type=t3.object_type;ExecutionPlan----------------------------------------------------------Planhashvalue:2308542799----------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------|0|SELECTSTATEMENT||7|210|9(0)|00:00:01||*1|HASHJOIN||7|210|9(0)|00:00:01||*2|HASHJOIN||7|140|6(0)|00:00:01||3|TABLEACCESSFULL|T1|1|10|3(0)|00:00:01||4|TABLEACCESSFULL|T3|21|210|3(0)|00:00:01||5|TABLEACCESSFULL|T2|11|110|3(0)|00:00:01|----------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("T2"."OBJECT_ID"="T3"."OBJECT_ID")2-access("T1"."OBJECT_TYPE"="T3"."OBJECT_TYPE")

加入以下hint，就解决:

SELECT/*+ordereduse_hash(t3)swap_join_inputs(t1)*/t1.owner,t2.object_name,t3.object_typeFROMt2,t3,t1WHEREt2.object_id=t3.object_id5ANDt1.object_type=t3.object_type;ExecutionPlan----------------------------------------------------------Planhashvalue:3071514789----------------------------------------------------------------------------|Id|Operation|Name|Rows|Bytes|Cost(%CPU)|Time|----------------------------------------------------------------------------|0|SELECTSTATEMENT||4|120|9(0)|00:00:01||*1|HASHJOIN||4|120|9(0)|00:00:01||2|TABLEACCESSFULL|T1|1|10|3(0)|00:00:01||*3|HASHJOIN||11|220|6(0)|00:00:01||4|TABLEACCESSFULL|T2|11|110|3(0)|00:00:01||5|TABLEACCESSFULL|T3|21|210|3(0)|00:00:01|----------------------------------------------------------------------------PredicateInformation(identifiedbyoperationid):---------------------------------------------------1-access("T1"."OBJECT_TYPE"="T3"."OBJECT_TYPE")3-access("T2"."OBJECT_ID"="T3"."OBJECT_ID")