如何使用Leading提示改變表連接方式

2024-07-21 02:38:11

字體：大中小

供稿：網(wǎng)友

　　在多表聯(lián)合查詢中,當(dāng)使用Ordered提示改變SQL執(zhí)行計(jì)劃之后,通常我們很難再次控制結(jié)果集中進(jìn)一步Join的順序.　　這時(shí)候我們可以使用Oracle提供的另外一個(gè)Hints: Leading 提示.　　這個(gè)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中表和結(jié)果集的Join順序.　　我們通過如下示例看一下這個(gè)提示是如何影響SQL執(zhí)行的: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) 　　我們看到,通過這兩個(gè)Hints的聯(lián)合使用,該查詢首先對(duì)T_MAX和T_MIDDLE表進(jìn)行HASH JOIN,再以這個(gè)結(jié)果集同T_SMALL進(jìn)行HASH JION.　　單純通過Ordered和USE_HASH提示通常是達(dá)不到這個(gè)效果的: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中的一個(gè)非凡用法.

上一篇：創(chuàng)建索引對(duì)SQL語句執(zhí)行的影響

下一篇：Data Guard數(shù)據(jù)庫災(zāi)難防護(hù)