玉面飞龙的BLOG

LEADING Hint change in 10g

August 9th, 2007 | Categories: Boring | Tags:

偶尔发现，提示leading，在10g中比在9i中好使唤多了.

LEADING Hint

The LEADING hint instructs the optimizer to [b]use the specified set of tables[/b] as [b]the prefix [/b]in the execution plan. This hint is more versatile than the ORDERED hint.

The LEADING hint is ignored if the tables specified cannot be joined first in the order specified because of dependencies in the join graph. If you specify two or more conflicting LEADING hints, then all of them are ignored. If you specify the ORDERED hint, it overrides all LEADING hints.

可以在leading中指定不止一个表，这样，多表连接的join order，就可以任意控制。比ordered灵活多了。

测试如下,

SQL> explain plan for

2 SELECT /*+ all_rows leading(t_large t_small t_medium) */COUNT (*)

3 FROM t_small, t_medium,t_large

4 WHERE t_small.id = t_medium.id

5 AND t_medium.id = t_large.id

6 and t_large.id = t_small.id ;

Explained.
PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------

Plan hash value: 3019969309

-----------------------------------------------------------------

| Id | Operation | Name | Rows | Bytes | Cost |

-----------------------------------------------------------------

| 0 | SELECT STATEMENT | | 1 | 39 | 5 |

| 1 | SORT AGGREGATE | | 1 | 39 | |

|* 2 | HASH JOIN | | 1 | 39 | 5 |

|* 3 | HASH JOIN | | 67 | 1742 | 3 |

| 4 | TABLE ACCESS FULL| T_LARGE | 82 | 1066 | 1 |

| 5 | TABLE ACCESS FULL| T_SMALL | 82 | 1066 | 1 |

| 6 | TABLE ACCESS FULL | T_MEDIUM | 82 | 1066 | 1 |

-----------------------------------------------------------------

Predicate Information (identified by operation id):

---------------------------------------------------

   2 - access("T_SMALL"."ID"="T_MEDIUM"."ID" AND

   "T_MEDIUM"."ID"="T_LARGE"."ID")

   3 - access("T_LARGE"."ID"="T_SMALL"."ID")

- cpu costing is off (consider enabling it)