直方图、基数、选择性、群集因子-阿里云开发者社区

开发者社区> 数据库> 正文

直方图、基数、选择性、群集因子

简介:

基本概念

基数(Cardinality) 列唯一键(Distinct_keys)的数量,比如性别,该列只有男女之分,所以这一列基数是2。

选择性(Selectivity) 列唯一键(Distinct_Keys)与行数(Num_Rows)的比值。

直方图 (Histogram)是一种对数据分布质量情况进行描述的工具。它会按照某一列不同值出现数量多少,以及出现的频率高低来绘制数据的分布情况,以便能够指导优化器根据数据的分布做出正确的选择。

频率直方图(FREQUENCY HISTOGRAM),当列中Distinct_keys 较少(小于254),如果不手工指定直方图桶数(BUCKET),Oracle就会自动的创建频率直方图,并且桶数(BUCKET)等于Distinct_Keys。

高度平衡直方图(HEIGHT BALANCED),当列中Distinct_keys大于254,如果不手工指定直方图桶数(BUCKET),Oracle就会自动的创建高度平衡直方图。

集群因子(Clustering Factor) 描述一个表中的列是否是规则排序的。

我们知道可以通过dbms_rowid.rowid_block_number(rowid)找到记录对应的block 号。索引中记录了rowid,因此oracle 就可以根据索引中的rowid来判断记录是否是在同一个block 中。举个例子,比如说索引中有a,b,c,d,e五个记录,首先比较a,b 是否在同一个block,如果不在同一个block 那么Clustering Factor +1,然后继续比较b,c 同理,如果b,c 不在同一个block,那么Clustering Factor+1,这样一直进行下去,直到比较了所有的记录。根据算法我们就可以知道clustering factor 的值介于block 数和表行数之间。如果clustering factor 接近block 数,说明表的存储和索引存储排序接近,也就是说表中的记录很有序,这样在做index range scan 的时候能,读取少量的data block 就能得到我们想要的数据,代价比较小。如果clustering factor 接近表记录数,说明表的存储和索引排序差异很大,在做index range scan 的时候,会额外读取多个block,因为表记录分散,代价较高。

1. 创建实验表
SQL> show user;
USER is "ANDY"

SQL>create table test as select * from dba_objects;

2. 先收集统计信息

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ANDY',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size skewonly',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/

说明:对于大表 estimate_percent 参数一般指定为 30% ,够CBO用就行。

补充内容
删统计信息 (这里不要操作,作为了解)
BEGIN
DBMS_STATS.GATHER_TABLE_STATS(ownname => 'ANDY',
tabname => 'TEST',
estimate_percent => 100,
method_opt => 'for all columns size 1',
no_invalidate => FALSE,
degree => 1,
cascade => TRUE);
END;
/

3.查询统计信息 (基数和选择性)

select a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity,
a.histogram,
a.num_buckets
from dba_tab_col_statistics a, dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'ANDY'
and a.table_name = 'TEST';

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
OWNER 74770 30 .04 FREQUENCY 30
OBJECT_NAME 74770 46694 62.45 HEIGHT BALANCED 254
SUBOBJECT_NAME 74770 51 .07 FREQUENCY 51
OBJECT_ID 74770 74770 100 NONE 1
DATA_OBJECT_ID 74770 9792 13.1 HEIGHT BALANCED 254
OBJECT_TYPE 74770 43 .06 FREQUENCY 43
CREATED 74770 1120 1.5 HEIGHT BALANCED 254
LAST_DDL_TIME 74770 1185 1.58 HEIGHT BALANCED 254
TIMESTAMP 74770 1240 1.66 HEIGHT BALANCED 254
STATUS 74770 2 0 FREQUENCY 2
TEMPORARY 74770 2 0 FREQUENCY 2

COLUMN_NAME NUM_ROWS CARDINALITY SELECTIVITY HISTOGRAM NUM_BUCKETS
------------------------------ ---------- ----------- ----------- --------------- -----------
GENERATED 74770 2 0 FREQUENCY 2
SECONDARY 74770 2 0 FREQUENCY 2
NAMESPACE 74770 20 .03 FREQUENCY 20
EDITION_NAME 74770 0 0 NONE 0

15 rows selected.

观察得到:
如果 CARDINALITY 基数小于254 ,那么 NUM_BUCKETS 桶数 就= 列基数 CARDINALITY。

总结:

1.
在OLTP系统中,基数/选择性高的列,适合建立B-Tree索引,选择性低的列不适合建立索引。
在OLAP环境中,基数低的列根据需求,可能会建立bitmap索引。

2.
没有直方图,CBO认为这个数据是分布均匀的,执行计划中估算返回的行数是基于列基数的平均值,
与实际返回的行数不符,可能产生错误的执行计划。

3.
什么时候该执行统计直方图操作 -> 执行计划估算的行数和实际查询返回的行数进行比较,如果相差很大,则需。

文章可以转载,必须以链接形式标明出处。


本文转自 张冲andy 博客园博客,原文链接: http://www.cnblogs.com/andy6/p/6582954.html  ,如需转载请自行联系原作者

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章