oracle 使用leading, use_nl, rownum調優例子

成本計算方法：
設小表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秒。