终级Hint:CARDINALITY

终级Hint:CARDINALITY

August 14th, 2009 | Categories:  Boring | Tags:  Oracle,  SQL tunning

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中的王道。