彻底搞懂Oracle直方图

Oracle优化器根据数据库对象统计信息来评估访问特定数据的成本，这些信息主要分为三个维度：

• 表、索引等对象级统计信息，包含表行数、平均行长、索引页块数量等，
• 列、字段等统计信息，包含字段的唯一值数量、空值数量、字段最大值最小值等；
• 数据字典、固定对象以及系统级的统计信息。

大多数情况下这些信息是够用的，但是当列数据分布不均匀时，优化器对于某些列值的评估不够准确，导致SQL执行效率问题。这就是直方图的设计初衷。

直方图的作用

优化器默认数据分布是均匀的，没有更详细的信息时，会基于唯一值来(1/NDV – Number of Distinct Values)评估数据的选择度。比如一张表有1000行数据，某个列的Num Distinct值为100，则其选择度为1/100，也就是说基于这个列的等值查询，优化器认为会返回10 (1000 * 1/100)行数据。
但现实世界往往没有这么简单，如果这张表是用来记录某个中学全年级学生考试成绩，现在需要分别统计分数为80分和30分的学生人数。按照实际的经验，0 ~ 50分的很少，95 ~ 100的可能也不多，大多数数据可能会位于70 ~ 90分之间。所以以下两条SQL的执行成本是相同的吗？

select count(*) from 成绩表 where 得分=80;
select count(*) from 成绩表 where 得分=10;

查询10分的学生人数可能是个位数，甚至为零，这个时候选择索引扫描是高效的；80分的学生人数则可能达到500人，占据这张表的80%数据量，这个时候使用全表扫描更加合理。

这个场景下，优化器如何来评估最优的执行计划呢？Oracle对这个问题的解决方案是直方图。
直方图是一种统计报告图，由一系列高度不等的纵向条纹或线段表示数据分布的情况，一般横轴表示数据类型，纵轴表示该类型数据出现的频率。针对数据分布不均匀的场景，Oracle利用直方图来表示字段值的分布信息，以帮助优化器评估出更优的路径。

直方图的分类

为了更好的应对不同的场景，Oracle 11g及之前的版本定义了两种类型的直方图：Frequency Histograms和Height Balanced Histograms。

Frequency Histograms

Frequency直方图最多使用254个Bucket (也称为 END POINT) 来存储数据的分布，当一张表的唯一值跨度小于254时，每个唯一值都可以用一个独立的Bucket来表示。比如上面的学生成绩表，最多只有100个不同值，这种直方图称为Frequency Histograms，能够精确的呈现每个值的分布情况。

Height Balanced Histograms

如果唯一值大于254，没有足够的Bucket表示所有的值，则需要采用稀疏策略，将数据划分为不同的区间对应到相应的Bucket中。Height Balanced直方图的每个Bucket包含相同数量的唯一值，通过每个Bucket中最高值和最低值来计算数据的分布。

举个例子，帮助大家更好的理解这两个概念。
准备1张测试表，其中b=5的数据9991条，b=1…4和9996…10000的值各1条。

drop table HTAB2;
create table HTAB2 (a number, b number);
create index HTAB1_B on HTAB1(b);

insert into HTAB1 ( A,B) values ( 1,1);
insert into HTAB1 ( A,B) values ( 2,2);
insert into HTAB1 ( A,B) values ( 3,3);
insert