Oracle收集直方图信息

          直方图在列数据分布不均匀时非常有用，查询优化器需要直方图信息才能做出正确的估算。有频度直方图与等高直方图两种。本篇依然使用上一篇的测试表，文章链接Oracle中收集表与列统计信息

一、频度直方图

          频度直方图使用的不是频度，而是使用累积频度。下面的endpoint_number是取值的累计次数。

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;ENDPOINT_VALUE ENDPOINT_NUMBER  FREQUENCY-------------- --------------- ----------           101               8          8           102              33         25           103             101         68           104             286        185           105             788        502           106            1000        212

          频度直方图的本质特征有：

①桶数（分类数）等于唯一值总数。

②列endpoint_value提供该本身。

③列endpoint_number是取值的累计出现次数。只有当前endpoint_number减去上一endpoint_number才是当前值的出现次数。

         下面演示查询优化器怎样使用频度直方图精确地估算出基于列val2过滤后查询返回的基数（cardinality）。

EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2=101;EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2=102;EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2=103;EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2=104;EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2=105;EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val2=106;SELECT STATEMENT_ID,CARDINALITY FROM plan_table WHERE ID=0;

STATEMENT_ID

CARDINALITY

101

8

102

25

103

68

104

185

105

502

106

212

          当列的唯一值的个数大于桶允许的最大数量（254）时，就不能使用频度直方图了，此时应该使用等高直方图。

一、等高直方图

          下面演示如何在val2列产生等高直方图。

SELECT COUNT(*), MAX(VAL2) AS ENDPOINT_NUMBER  FROM (SELECT VAL2, NTILE(5) OVER(ORDER BY VAL2) AS ENDPOINT_NUMBER FROM T) GROUP BY ENDPOINT_NUMBER ORDER BY ENDPOINT_NUMBER;  COUNT(*) ENDPOINT_NUMBER---------- ---------------       200             104       200             105       200             105       200             106       200             106

         这表示最小值到104之间有200个数，104到105有200个数，105到105有200个数，依此类推。也就是说每个桶上放的数是均匀的。上面的查询分配了5个桶，共装1000个数，平均一个桶装200个数。

         然而在数据字典中存储等高直方图信息时，端点相同并且紧邻的桶是没有的，它存储的是一个压缩值。在直方图中多次出现的值被称为频繁值。

         等高直方图的特征：

①  桶数少于唯一值总数。

②  端点值就是列的数值。这里要注意一点，非数字类型必须进行转换，此值仅取前六个字节。

③  endpoint_number列给出了桶号。

④  等高直方图不存储一个取值的频度。