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

基本概念

基数(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.
什么时候该执行统计直方图操作 -> 执行计划估算的行数和实际查询返回的行数进行比较，如果相差很大，则需。