|
在多表联合查询中当使用Ordered提示改变SQL执行计划之后通常我们很难再次控制结果集中进一步Join的顺序 这时候我们可以使用Oracle提供的另外一个Hints: Leading 提示 这个Hints在Oraclei中的含义为: The LEADING hint causes Oracle to use the specified table as the first table in the join order If you specify two or more LEADING hints on different tables then all of them are ignored If you specify the ORDERED hint then it overrides all LEADING hints
通过Leading 和 use_hash 提示连用我们可以巧妙的影响SQL中表和结果集的Join顺序 我们通过如下示例看一下这个提示是如何影响SQL执行的: SQL> SELECT /*+ leading(t_max) use_hash(t_max t_middle) */ COUNT (*) FROM t_small t_max t_middle WHERE t_smallobject_id = t_middleobject_id AND t_middleobject_id = t_maxobject_id / Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) SORT (AGGREGATE) HASH JOIN (Cost= Card= Bytes=) HASH JOIN (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T_MAX (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T_MIDDLE (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T_SMALL (Cost= Card= Bytes=) 我们看到通过这两个Hints的联合使用该查询首先对T_MAX和T_MIDDLE表进行HASH JOIN再以这个结果集同T_SMALL进行HASH JION 单纯通过Ordered和USE_HASH提示通常是达不到这个效果的: SQL> SELECT /*+ ordered use_hash(t_max t_middle) */ COUNT (*) FROM t_max t_middlet_small WHERE t_smallobject_id = t_middleobject_id AND t_middleobject_id = t_maxobject_id / Execution Plan
SELECT STATEMENT Optimizer=CHOOSE (Cost= Card= Bytes=) SORT (AGGREGATE) HASH JOIN (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T_SMALL (Cost= Card= Bytes=) HASH JOIN (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T_MAX (Cost= Card= Bytes=) TABLE ACCESS (FULL) OF T_MIDDLE (Cost= Card= Bytes=) 这是Leading Hints在Oraclei中的一个特殊用法 |
