如何使用ordered提示改变SQL执行计划

tomthe

ORDERED提示强制Oracle按照From子句中表出现的顺序进行表连接。
通过ordered提示，可以避免CBO SQL解析过程中的表连接评估，从而避免Oracle产生错误的执行计划，或者强制Oracle按照我们指定的方式执行。
在很多时候，当我们清楚地了解数据结构和数据分布之后，就可以通过ORDERED提示来提高SQL性能。
通过以下例子我们来说明一下Ordered提示的作用.
1.不加Hints时SQL的执行计划

SQL> set autotrace trace explainSQL>  SELECT COUNT (*)  2    FROM t_small, t_max, t_middle  3   WHERE t_small.object_id = t_middle.object_id  4   AND t_middle.object_id = t_max.object_id;Execution Plan----------------------------------------------------------   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=194 Card=1 Bytes=12)   1    0   SORT (AGGREGATE)   2    1     HASH JOIN (Cost=194 Card=400 Bytes=4800)   3    2       HASH JOIN (Cost=42 Card=100 Bytes=800)   4    3         TABLE ACCESS (FULL) OF 'T_SMALL' (Cost=2 Card=100 Bytes=400)   5    3         TABLE ACCESS (FULL) OF 'T_MIDDLE' (Cost=39 Card=28447 Bytes=113788)   6    2       TABLE ACCESS (FULL) OF 'T_MAX' (Cost=151 Card=113792 Bytes=455168)

我们可以通过10053事件跟踪一下该SQL的解析:

SQL> alter session set events='10053 trace name context forever,level 1';Session altered.SQL> explain plan for  2  SELECT COUNT (*)  3    FROM t_small, t_max, t_middle  4  WHERE t_small.object_id = t_middle.object_id  5  AND t_middle.object_id = t_max.object_id;    Explained.

查看Trace文件可以看到,Oracle需要进行3! (6)次表连接顺序的评估:

bash-2.03$ cat testora9_ora_10862.trc |grep "Join order"Join order[1]: T_SMALL [T_SMALL] T_MIDDLE [T_MIDDLE] T_MAX [T_MAX] Join order[2]: T_SMALL [T_SMALL] T_MAX [T_MAX] T_MIDDLE [T_MIDDLE] Join order[3]: T_MIDDLE [T_MIDDLE] T_SMALL [T_SMALL] T_MAX [T_MAX] Join order[4]: T_MIDDLE [T_MIDDLE] T_MAX [T_MAX] T_SMALL [T_SMALL] Join order[5]: T_MAX [T_MAX] T_SMALL [T_SMALL] T_MIDDLE [T_MIDDLE] Join order[6]: T_MAX [T_MAX] T_MIDDLE [T_MIDDLE] T_SMALL [T_SMALL]

2.当我们使用Ordered提示之后
SQL的执行计划如下(from子句后的表顺序作了调整):

SQL> SELECT /*+ ordered */ COUNT (*)  2    FROM t_middle, t_small, t_max  3  WHERE t_small.object_id = t_middle.object_id  4  AND t_middle.object_id = t_max.object_id;  Execution Plan----------------------------------------------------------   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=197 Card=1 Bytes=12)   1    0   SORT (AGGREGATE)   2    1     HASH JOIN (Cost=197 Card=400 Bytes=4800)   3    2       HASH JOIN (Cost=45 Card=100 Bytes=800)   4    3         TABLE ACCESS (FULL) OF 'T_MIDDLE' (Cost=39 Card=28447 Bytes=113788)   5    3         TABLE ACCESS (FULL) OF 'T_SMALL' (Cost=2 Card=100 Bytes=400)   6    2       TABLE ACCESS (FULL) OF 'T_MAX' (Cost=151 Card=113792 Bytes=455168)

再看10053的跟踪Trace文件:

bash-2.03$ grep "Join order" testora9_ora_10918.trcJoin order[1]: T_MIDDLE [T_MIDDLE] T_SMALL [T_SMALL] T_MAX [T_MAX]

Oracle只需要按照表在From子句中的出现顺序进行连接,从而按照我们的意图进行解析或执行.
这就是Ordered提示的基本作用,本例只是一个示范说明,后者的执行计划使得Cost激增,在实际应用中,我们当然是不希望看到此类增长的.
 
 
 
 
下面所介绍的提示并不需要经常使用，当对优化器为某个查询语句所制定的基本执行计划不满意时，最好的办法就是通过使用提示来转换优化器的模式，并观察其转换后的结果，看是否已经达到我们期望的程度。如果只通过转换优化器的模式就可以获得非常好的执行计划，则就没有必要额外使用更为复杂的提示了。
ALL_ROWS
为实现查询语句整体结果最优化而引导优化器制定最少成本的执行计划。（Minimize total resource consumption）
例：SELECT /*+ ALL_ROWS */…
CHOOSE
依据SQL中所使用到的表的统计信息存在与否，来决定使用RBO还是CBO。在CHOOSE模式下，如果能够参考表的统计信息，则将按照ALL_ROWS方式执行。
例： SELECT /*+ CHOOSE */…
FIRST_ROWS
为获得最佳响应时间而引导优化器制定最少成本的执行计划。（Minimum resource usage to return first or firt n rows）
例： SELECT /*+ FIRST_ROWS */…
SELECT /*+ FIRST_ROWS (10) */…
RULE
使用基于规则的优化器来实现最优化执行，即引导优化器根据优先顺序规则来决定查询条件中所使用到的索引或运算符的执行顺序来制定执行计划。
例：SELECT /*+ RULE */…
 
 
 
一般而言，这里所介绍的提示主要在执行多表连接和表之间的连接顺序比较混乱的情况下才使用；也在排序合并连接（Sort Merge Join）方式或哈希连接（Hash Join）方式下，为引导优化器优先执行数据量比较少的表时使用。
ORDERED
引导优化器按照FROM中所描述的表的顺序执行连接。如果和LEADING提示被一起使用，则LEADING提示将被忽略。
例： SELECT /*+ ORDERED */…
FROM TAB1, TAB2, TAB3
WHERE …
由于ORDERED只能够调整表连接的顺序而并不能改变表连接的方式，所以为了改变表的连接方式，经常将USE_NL、USE_MERGE提示与ORDERED提示放在一起使用。
例： SELECT /*+ ORDERED USE_NL(A B C) */…
FROM TAB1 a, TAB2 b, TAB3 c
WHERE …
LEADING
引导优化器使用LEADING指定的表作为表连接顺序中的第一个表。该提示既与FROM中所描述的表的顺序无关，也与作为调整表连接顺序的ORDERD提示不同，并且在使用该提示时并不需要调整FROM中所描述的表的顺序。当该提示与ORDERED提示同时使用时，该提示被忽略。
例：SELECT /*+ LEADING (b c) */…
FROM CUST a, ORDER_DETAIL b, ITEM c
WHERE a.cust_no = b.cust_no
AND b.item_no = c.item_no
AND …