终级Hint:CARDINALITY

August 14th, 2009 | Categories: Boring | Tags: ,
Leave a comment | Trackback

这个hint实在是Oracle SQL tunning的终极杀手锏，用我们对数据分布的理解强制给Oracle洗脑。用在多表连接或者存在in-list & exists的时候，选择驱动表，尤其适合。

This hint specifies the estimated cardinality returned by a query or portions of the query. Note if no table is specified, the cardinality is the total number of rows returned by the entire query.

For example:

SELECT /*+ CARDINALITY ( [tablename] card ) */

比方说如下表连接SQL, 往往会选择先读取 EMPLOYEES地记录，再通过USER_ID上的索引与表EMPLOYEES_ASSET进行连接。

SELECT * FROM EMPLOYEES WHERE level between :1 and :2 and
USER_ID in
(SELECT USER_ID FROM EMPLOYEES_ASSET WHERE IS_PROCESSED = 0 );

但我们如果事先就知道，表EMPLOYEES_ASSET实际上满足“IS_PROCESSED = 0”的记录很少，则可以让 EMPLOYEES_ASSET作驱动表，能够得到更小的中间结果集。这时候我们添加　hint  /*+ CARDINALITY(EMPLOYEES_ASSET  1) */,则Oracle CBO计算得知EMPLOYEES_ASSET做驱动表将成本更低。

虽然很多hint也能够起到类似的作用　CARDINALITY仍然是hint中的王道。

Del.icio.us Google书签 Digg Live Bookmark Technorati Furl Yahoo书签 Facebook 百度搜藏 新浪 ViVi 365Key 网摘 天极网摘 和讯网摘 博拉网 POCO 网摘 饭否 QQ 书签 Digbuzz 我挖网 Mister Wong
  1. victor666666
    August 17th, 2009 at 13:05
    Quote | #1

    用过一次，感觉很管用，特别是在内存表、临时表等CBO不知道（也没办法知道）的情况下尤其有用啊