oracle 使用leading, use_nl, rownum调优

1、使用leading和use_nl来设置表的查询顺序，来加快查询速度，一般把小表设为第一个表。
/*+LEADING(TABLE)*/
　　将指定的表作为连接次序中的首表.
/*+USE_NL(TABLE)*/
　　将指定表与嵌套的连接的行源进行连接,并把指定表作为内部表.

成本计算方法：
设小表100行，大表100000行。

两表均有索引：
如果小表在内，大表在外(驱动表)的话，则扫描次数为：
100000+100000*2 (其中2表示IO次数，一次索引，一次数据)
如果大表在内，小表在外(驱动表)的话，则扫描次数为：
100+100*2.

两表均无索引：
如果小表在内，大表在外的话，则扫描次数为：
100000+100*100000
如果大表在内，小表在外的话，则扫描次数为：
100+100000*100

注意：如果一个表有索引，一个表没有索引，ORACLE会将没有索引的表作驱动表。如果两个表都有索引，则外表作驱动表。如果两个都没索引的话，则也是外表作驱动表。

2、使用index直接匹配索引来查询数据提高查询速度
/*+INDEX(TABLE INDEX_NAME)*/
　　表明对表选择索引的扫描方法.
3、当判断某几个表中是否存在某种关系的行时可使用rownum=1来作为条件而加快速度，如果必须有多行才满足条件时，可设置rownum <= n。
例如：获得一个v_count值判断是否大于0
equipment表 几万条，controledpnsnrange 几千条
select
count(sc.pmnum)
into
v_count
from
equipment e,
sal_controledpnsnrange sc
where
(sc.new_min_item_no <= e.itemnum)
and (sc.new_max_item_no >= e.itemnum)
and (sc.new_min_serial_no <= e.serialnum)
and (sc.new_max_serial_no >= e.serialnum)
and e.itemnum = a_item_no;
执行时间大约为3分钟。
代码修改后:
select /*+ leading(e) use_nl(sc) */
count(sc.pmnum)
into
v_count
from
equipment e,
sal_controledpnsnrange sc
where
(sc.new_min_item_no <= e.itemnum)
and (sc.new_max_item_no >= e.itemnum)
and (sc.new_min_serial_no <= e.serialnum)
and (sc.new_max_serial_no >= e.serialnum)
and e.itemnum = a_item_no;
执行时间大约为45秒。
代码再次修改后：
select /*+ leading(e) use_nl(sc) */
count(sc.pmnum)
into
v_count
from
equipment e,
sal_controledpnsnrange sc
where
(sc.new_min_item_no <= e.itemnum)
and (sc.new_max_item_no >= e.itemnum)
and (sc.new_min_serial_no <= e.serialnum)
and (sc.new_max_serial_no >= e.serialnum)
and e.itemnum = a_item_no
and rownum = 1;
执行时间大约为4秒。

 

在多表联合查询中,当使用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中的一个特殊用法.