4. 直方图
查询优化器原则上认为某一列上的数据是分布均匀的,比如id列,存储的是1到1000的数字,每个值一次。这种情况要找到满足条件的数据行(比如 id between 3 and 19),查询优化器只需要知道id列的最大值,最小值和唯一值总数即可估算大概要查询出多少数据。因为如果超过了20%的数据就不会走索引扫描,因为索引扫描在这种情况比全表扫描更慢。
但是,如果数据分布不均匀,那么就需要直方图来帮助查询优化器做出正确选择。比如val2列就是分布不均的,运行下面的SQL可以看出来
SELECT val2 AS val2, count(*)
FROM t
GROUP BY val2
ORDER BY val2;
结果如下图:
可以看出val2列总共只取6个值,取值105的最多有502个,取值101的最少,只有8个。
直方图存在两种:频度直方图(frequency histogram)和等高直方图(height-balanced histogram)。从列统计信息可以看出val2列属于频度直方图,模型可能如下图所示:
查看直方图信息SQL:
SELECT endpoint_value, endpoint_number,
endpoint_number - lag(endpoint_number,1,0)
OVER (ORDER BY endpoint_number) AS frequency
FROM user_tab_histograms
WHERE table_name = 'T'
AND column_name = 'VAL2'
ORDER BY endpoint_number;
结果如下图所示:
第一列:val2列的值,第二列:累计出现次数,第三列:该值出现次数。比如val2取值105的行数为502行,小于等于105的行数为788行。根据val2直方图信息,我们就很容易估算出val2取值在某一个范围内的行数。
当一列的唯一值数量大于桶允许的最大数254的时候,频度直方图就不适合了。这个时候需要使用等高直方图,这里就不介绍了。