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