等频直方图，计算COST

--------------数据库版本
SQL> select * from v$version where rownum=1;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

---------------表中数据分布
SQL> select count(*),id from wxh_tbd group by id;

                      COUNT(*)                             ID
------------------------------ ------------------------------
                        398663                         100000
                         49834                             10
---------------收集直方图信息
begin
  dbms_stats.gather_table_stats(ownname          => 'scott',
                                tabname          => 'wxh_tbd',
                                no_invalidate    => FALSE,
                                estimate_percent => 100,
                                method_opt       => 'for all  columns size skewonly',
                                cascade          => true);
end;
/
SQL> SELECT column_name,endpoint_number,endpoint_value FROM USER_HISTOGRAMS WHERE TABLE_NAME='WXH_TBD';

COLUMN_NAME                    ENDPOINT_NUMBER ENDPOINT_VALUE
------------------------------ --------------- --------------
ID                                       49834             10
ID                                      448497         100000
--------------在ID列上创建索引
create index t_d on wxh_tbd(id);
--------------执行查询
SQL> select /*+ index(wxh_tbd t_d) */count(*) from wxh_tbd where id=10;

                      COUNT(*)
------------------------------
                         49834


执行计划
----------------------------------------------------------
Plan hash value: 485739739

----------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost  |
----------------------------------------------------------
|   0 | SELECT STATEMENT  |      |     1 |     3 |   100 |
|   1 |  SORT AGGREGATE   |      |     1 |     3 |       |
|*  2 |   INDEX RANGE SCAN| T_D  | 49834 |   145K|   100 |
----------------------------------------------------------

可以看出COST是100
本例中索引的LEVEL 是2
索引页块数量是857.
表总行数是448497
根据索引COST公式
COST=blevel +ceiling(leaf_blocks * effective index selectivity)
=2+ceiling(857*effective index selectivity)
关键是看这个effective index selectivity怎么计算。
在列没有直方图的情况下，一般等于1/num_distinct
如果谓词列存在直方图，就不能采取1/num_distinct了。
我查了好多资料，都没查到算法，CBO书里也没给出具体的公式。可能是他觉得太简单了？
那么猜一下。既然直方图都把对应列的频率统计出来了，那么拿这个频率除以总行数，不就是选择率吗？
验证下
SQL> select ceil((49834/448497)*875)+2 from dual;

CEIL((49834/448497)*875)+2
--------------------------
                       100
果然不出所料。

[ 本帖最后由 wei-xh 于 2011-5-14 14:58 编辑 ]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-695365/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-695365/