Oracle HowTo:如何使用Leading提示改变表连接方式

« 逻辑严谨与数据安全 | Blog首页 | 基于主键和唯一约束的显示索引控制 »

在多表联合查询中,当使用Ordered提示改变SQL执行计划之后,通常我们很难再次控制结果集中进一步Join的顺序.

这时候我们可以使用Oracle提供的另外一个Hints: Leading 提示.

这个Hints在Oracle9i中的含义为:

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 (*)
  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
  5  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=262 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=262 Card=400 Bytes=4800)
   3    2       HASH JOIN (Cost=225 Card=113776 Bytes=910208)
   4    3         TABLE ACCESS (FULL) OF 'T_MAX' (Cost=151 Card=113792 Bytes=455168)
   5    3         TABLE ACCESS (FULL) OF 'T_MIDDLE' (Cost=39 Card=28447 Bytes=113788)
   6    2       TABLE ACCESS (FULL) OF 'T_SMALL' (Cost=2 Card=100 Bytes=400)

我们看到,通过这两个Hints的联合使用,该查询首先对T_MAX和T_MIDDLE表进行HASH JOIN,再以这个结果集同T_SMALL进行HASH JION.

单纯通过Ordered和USE_HASH提示通常是达不到这个效果的:

SQL> SELECT /*+ ordered use_hash(t_max t_middle) */ COUNT (*)
  2    FROM  t_max, t_middle,t_small
  3  WHERE t_small.object_id = t_middle.object_id
  4  AND t_middle.object_id = t_max.object_id
  5  /

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=228 Card=1 Bytes=12)
   1    0   SORT (AGGREGATE)
   2    1     HASH JOIN (Cost=228 Card=400 Bytes=4800)
   3    2       TABLE ACCESS (FULL) OF 'T_SMALL' (Cost=2 Card=100 Bytes=400)
   4    2       HASH JOIN (Cost=225 Card=113776 Bytes=910208)
   5    4         TABLE ACCESS (FULL) OF 'T_MAX' (Cost=151 Card=113792 Bytes=455168)
   6    4         TABLE ACCESS (FULL) OF 'T_MIDDLE' (Cost=39 Card=28447 Bytes=113788)

这是Leading Hints在Oracle9i中的一个特殊用法.

历史上的今天...
>> 2011-02-26文章:

我的写作和博文视点及51CTO的评选

>> 2009-02-26文章:

IBM AIX Read-only file system案例一则

《深入解析Oracle》DBA 2.0时代技术交流会

>> 2005-02-26文章:

让B*Tree索引一直向左走

如何转储B*Tree索引的分枝结构

5 Comments

kingheap | February 26, 2006 12:20 PM

大师，您好！每天早上都会在第一时间登录您的blog，每天都会有新的收获，谢谢您为oracle world做的贡献！
对于leading hint的结果，我一直以为可以通过这样的方式得到和leading同样的explain plan，但刚刚的实验不是这样:(
SQL> SELECT /*+ ordered use_hash(t_max t_middle) use_hash(t_middle,t_small) */ COUNT (*)
2 FROM t_max, t_middle ,t_small
3 WHERE t_small.object_id = t_middle.object_id
4 AND t_middle.object_id = t_max.object_id
5 /

Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=33 Card=1 Bytes=39)
1 0 SORT (AGGREGATE)
2 1 HASH JOIN (Cost=33 Card=50 Bytes=1950)
3 2 TABLE ACCESS (FULL) OF 'T_SMALL' (Cost=2 Card=50 Bytes
4 2 HASH JOIN (Cost=30 Card=5000 Bytes=130000)
5 4 TABLE ACCESS (FULL) OF 'T_MAX' (Cost=6 Card=29561 By
6 4 TABLE ACCESS (FULL) OF 'T_MIDDLE' (Cost=2 Card=5000

我想问一下大师，为什么使用ordered和use_hash提示不行呢，use_hash的第一个表应该是驱动表呀。

我刚刚看了itpub的文章，可是这个文章好像没有讨论结束http://www.itpub.net/223095,1.html

谢谢！！！

eygle | February 26, 2006 6:13 PM

那个讨论完成了.

单纯以Ordered + Use_hash 很难实现以上效果.
根据查询条件,很多时候USE_HASH hints会被忽略.

郁郁葱葱 | February 27, 2006 11:42 AM

学习!!

zf_wu | March 1, 2006 10:26 AM

对９i而言，Leading 只能指定第一个join table,对于4个甚至更多的表并不很有效.只有star可以，不过也只能适合特定的环境．几乎没有什么办法，可以完整控制多表连接方法．请指正！

eygle | March 2, 2006 9:09 AM

zf_wu,是的,对于多表(3表以上)的连接,很难严格控制连接方式.

而实际上,这种控制也是意义不大的,Oracle的理念是要我们逐渐的,更加充分的信任优化器的作用.