Oracle Histogram 基础介绍
版本 | 修订人 | 修订日期 | 备注 |
---|---|---|---|
v0.1 | yangze.yz | 2016-10-8 | Oracle Histogram 基础介绍 |
直方图目的
解决数据倾斜情况:假设表t(c1)有1000的数据量,NDV:10,其中num:0数据量占910,其他1-9分别为10。在没有直方图的情况下,c1 = 0-9都会估计有100行。
什么时候创建直方图
- 手动模式
exec DBMS_STATS.GATHER_TABLE_STATS(ownname => ‘SYS’, tabname => ‘T’, method_opt => ‘FOR COLUMNS C1 SIZE 200, C2 SIZE AUTO’);
指定了c1创建bucket num为200的histogram;c2 size auto,采用自动方式.如果仅写c1,那么c2不创建histogram。
- 自动模式
(1)参数METHOD_OPT 为SIZE_AUTO,默认值。
(2)用户query查询了表。
(3)数据库检查query中条件涉及的column,并更新SYS.COL_USAGE$表作为记录。
(4)DBMS_STATS 收集统计信息,通过SYS.COL_USAGE$来决定是否需要考虑建立直方图。
Oracle如何选择直方图类型
- NDV: number of distinct values
- n: buckets num。默认254。
- p: (1 - (1/n)) * 100。
Figure:Decision Tree for Histogram Creation
名词解释
Endpoint Numbers and values
Endpoint Number 用来唯一标识一个桶。在频率(frequency)和混合(hybrid)直方图中,Endpoint number标识从前一个桶和当前桶所有值的累积频率。在高度直方图中,是从0或1开始的连续数字,表示第几个桶。频率直方图可以看成高度为1的高度直方图,将相同Endpoint Value的bucket压缩后的结果。
Endpoint Value 表示桶中最大的那一个值。
Popular and Nonpopular Values
Popular value影响行数估计算法。
- popular value 多个bucket有同一个Endpoint Value,那么这个值就是popular value。优化器估算popular value的cardinality时候使用如下公式:
cardinality of popular value =
(num of rows in table) *
(num of endpoints spanned by this value / total num of endpoints)
- Nonpopular value 所有非popular的数据就是Nonpopular value。优化器估算popular value的cardinality时候使用如下公式:
cardinality of nonpopular value =
(num of rows in table) * density
density综合资料和实际应该是
density =(总的buckets数- 所有popular值的buckets数PopBktCnt)
/总的buckets数
/ (NDV- popular值总的个数POPVALCNT)
频率直方图(Frequency Histograms)
每个column value对应着一个bucket。
从Oracle 12c起,当采样设置为AUTO_SAMPLE_SIZE的时候,database会通过全表扫描创建频率直方图。在早期版本中,会用一个较小的采样来创建,但这会使部分小频率的值没有在采样中出现,使用density计算选择率会偏大。
Top Frequency Histograms
Top Frequency Histograms是Frequency Histograms的一个变化,忽略统计上无关紧要的nonpopular values。如果一小部分数值覆盖了大部分行,那么创建就可以针对这一小部分数值创建频率直方图。
等高直方图(Height-Balanced Histograms)
Height-Balanced Histograms是将所有的行排序等分到每一个bucket中。
在12c之前,当NDV大于bucket数目的时候就会创建Height-Balanced Histogram,这种类型的直方图对于范围查询还有至少作为endpoint出现在两个bucket中的等值估计有效。
在升级12c之后,如果是AUTO_SAMPLE_SIZE,那么就不再创建Height-Balanced Histogram。
个人理解Height-Balanced Histogram对于部分很接近popular value的估算很不准确,hybrid histogram是它的一种改进。但由于hybrid histogram需要endpoint value的frequency准确,当指定采样率的时候会不能保证做到这一点。
Hybrid Histograms
上面提到了Height-Balanced Histograms对于接近popular value的数据估计会很不准确,比如一个值作为一个bucket的endpoint,但实际其覆盖了接近两个bucket。为了解决这个问题就引入了Hybrid Histogram,没有值会跨越两个bucket,同时记录endpoint的repeat count。这样优化器可以获取大部分popular value的准确选择率。
Hybrid Histograms会先按照Height-Balanced Histogram创建,创建之后再将跨越两个bucket的值移到同一个bucket作为endpoint,并记录repeat count。
优化器计算选择率的时候,对于endpoint value,使用其repeat count来计算选择率。对于非endpoint value就使用density。