SQL调优指南笔记11：Histograms

本文为SQL Tuning Guide第11章“Histograms”的笔记。

重要基本概念

• endpoint value
  An endpoint value is the highest value in the range of values in a histogram bucket.
  端点值是直方图桶中值范围内的最大值。

• popular value
  In a histogram, any value that spans two or more endpoints. Any value that is not popular is an nonpopular value.
  在直方图中，跨越两个或多个端点的任何值。

• nonpopular value
  In a histogram, any value that does not span two or more endpoints.
  在直方图中，任何不跨越两个或以上端点的值。

• density
  A decimal number between 0 and 1 that measures the selectivity of a column. Values close to 1 indicate that the column is unselective, whereas values close to 0 indicate that this column is more selective.
  一个介于 0 和 1 之间的十进制数，用于衡量列的选择性。 接近 1 的值表示该列是非选择性的，而接近 0 的值表示该列更具选择性。

直方图是一种特殊类型的列统计信息，可提供有关表列中数据分布的更详细信息。 直方图将值分类到“桶”中，就像您可能将硬币分类到桶中一样。

根据 NDV 和数据分布，数据库选择要创建的直方图类型。 （在某些情况下，在创建直方图时，数据库会对内部预先确定的行数进行采样。）直方图的类型如下：

• 频率直方图和最高频率直方图
• 高度平衡直方图（过时）
• 混合直方图

11.1 Purpose of Histograms

默认情况下，优化器假定行在列中不同值的均匀分布。

对于包含数据倾斜（列内数据的非均匀分布）的列，直方图使优化器能够为涉及这些列的过滤器和连接谓词生成准确的基数估计。

例如，一家位于加利福尼亚的书店将 95% 的书籍运送到加利福尼亚，4% 到俄勒冈州，1% 到内华达州。 book orders 表有 300,000 行。 表列存储订单发货的状态。 用户查询运往俄勒冈州的图书数量。 如果没有直方图，优化器假设均匀分布为 300000/3（NDV 为 3），估计 100,000 行的基数。 根据这个估计，优化器选择全表扫描。 使用直方图，优化器计算出 4% 的书籍被运送到俄勒冈州，并选择索引扫描。

11.2 When Oracle Database Creates Histograms

如果 DBMS_STATS 为一个表收集统计信息，并且如果查询已经引用了该表中的列，那么 Oracle 数据库会根据之前的查询工作负载自动创建直方图。

基本流程如下：

• 您对 METHOD_OPT 参数设置为默认 SIZE AUTO 的表运行 DBMS_STATS。
• 用户查询该表。
• 数据库记录前面查询中的谓词并更新数据字典表 SYS.COL_USAGE$。
• 您再次运行 DBMS_STATS，导致 DBMS_STATS 查询 SYS.COL_USAGE$ 以根据先前的查询工作负载确定哪些列需要直方图。

AUTO 功能的后果包括：

随着查询随时间变化，DBMS_STATS 可能会改变它收集的统计信息。 例如，即使表中的数据没有更改，查询和 DBMS_STATS 操作也会导致引用这些表的查询计划发生更改。

如果您收集表的统计信息但不查询该表，则数据库不会为该表中的列创建直方图。 要使数据库自动创建直方图，您必须运行一个或多个查询来填充 SYS.COL_USAGE$ 中的列使用信息。

示例：

CREATE TABLE sales2 AS SELECT * FROM sales;
CREATE INDEX sh_12c_idx1 ON sales2(prod_id);
CREATE INDEX sh_12c_idx2 ON sales2(cust_id,time_id);

-- 您查询数据字典以确定 sales2 列是否存在直方图。 因为sales2还没有被查询到，所以数据库还没有创建直方图
SELECT COLUMN_NAME, NOTES, HISTOGRAM 
FROM   USER_TAB_COL_STATISTICS 
WHERE  TABLE_NAME = 'SALES2';

COLUMN_NAME   NOTES          HISTOGRAM
------------- -------------- ---------
AMOUNT_SOLD   STATS_ON_LOAD  NONE
QUANTITY_SOLD STATS_ON_LOAD  NONE
PROMO_ID      STATS_ON_LOAD  NONE
CHANNEL_ID    STATS_ON_LOAD  NONE
TIME_ID       STATS_ON_LOAD  NONE
CUST_ID       STATS_ON_LOAD  NONE
PROD_ID       STATS_ON_LOAD  NONE

-- 您查询 sales2表 以获取产品ID为 42 的行数
SELECT COUNT(*) FROM sales2 WHERE prod_id = 42;

-- 然后使用 GATHER AUTO 选项收集表统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER,'SALES2',OPTIONS=>'GATHER AUTO');

-- 数据字典的查询现在显示数据库根据在前面查询期间收集的信息在 prod_id 列上创建了一个直方图
SELECT COLUMN_NAME, NOTES, HISTOGRAM 
FROM   USER_TAB_COL_STATISTICS 
WHERE  TABLE_NAME = 'SALES2';

COLUMN_NAME   NOTES          HISTOGRAM
------------- -------------- ---------
AMOUNT_SOLD   STATS_ON_LOAD  NONE
QUANTITY_SOLD STATS_ON_LOAD  NONE
PROMO_ID      STATS_ON_LOAD  NONE
CHANNEL_ID    STATS_ON_LOAD  NONE
TIME_ID       STATS_ON_LOAD  NONE
CUST_ID       STATS_ON_LOAD  NONE
PROD_ID       HISTOGRAM_ONLY FREQUENCY

11.3 How Oracle Database Chooses the Histogram Type

Oracle 数据库使用多个标准来确定要创建的直方图：频率、最高频率、高度平衡或混合。

直方图公式使用以下变量：

• NDV
  这表示列中不同值的数量。 例如，如果一列仅包含值 100、200 和 300，则该列的 NDV 为 3。
• n
  该变量表示直方图桶的数量。 默认值为 254。
• p
  此变量表示等于 (1–(1/n)) * 100 的内部百分比阈值。例如，如果 n = 254，则 p 为 99.6。

另一个标准是 DBMS_STATS 统计信息收集过程中的 estimate_percent 参数是否设置为 AUTO_SAMPLE_SIZE（默认值）。

下图显示了用于创建直方图的决策树。

11.4 Cardinality Algorithms When Using Histograms

对于直方图，基数算法取决于端点数量和值等因素，以及列值是受欢迎的还是不受欢迎的。

11.4.1 Endpoint Numbers and Values

端点编号是唯一标识存储桶的编号。 在频率和混合直方图中，端点编号是当前和先前存储桶中包含的所有值的累积频率。

例如，端点编号为 100 的存储桶表示当前和所有先前存储桶中值的总频率为 100。在高度平衡直方图中，优化器从 0 或 1 开始按顺序编号存储桶。在所有情况下，端点编号 是桶号。

端点值是存储桶中值范围内的最大值。 例如，如果存储桶仅包含值 52794 和 52795，则端点值为 52795。

11.4.2 Popular and Nonpopular Values

直方图中某个值的流行度会影响基数估计算法。

具体来说，基数估计受到以下影响：

• 流行值

一个流行的值作为多个存储桶的端点值出现。 优化器通过首先检查它是否是存储桶的端点值来确定一个值是否流行。 如果是这样，那么对于频率直方图，优化器从当前桶的端点号中减去前一个桶的端点号。 混合直方图已经为每个端点单独存储了此信息。 如果这个值大于 1，那么这个值是受欢迎的。

优化器使用以下公式计算流行值的基数估计：

cardinality of popular value = 
  (num of rows in table) * 
  (num of endpoints spanned by this value / total num of endpoints)

• 非流行值

任何不属于流行值的都是非流行值。 优化器使用以下公式计算非流行值的基数估计：

cardinality of nonpopular value = 
  (num of rows in table) * density

优化器使用基于桶数和 NDV 等因素的内部算法计算密度。 密度表示为 0 和 1 之间的十进制数。接近 1 的值表示优化器期望在其谓词列表中引用该列的查询返回许多行。 接近 0 的值表示优化器期望返回的行数很少。

11.4.3 Bucket Compression

在某些情况下，为了减少桶的总数，优化器会将多个桶压缩成一个桶。

例如，下面的频率直方图表示第一个桶号为 1，最后一个桶号为 23：

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              1          52792
              6          52793
              8          52794 
              9          52795
             10          52796
             12          52797
             14          52798
             23          52799

有几个桶“丢失”。 最初，桶 2 到 6 每个都包含一个值 52793 的实例。优化器将所有这些桶压缩到具有最高端点号的桶（桶 6）中，现在包含 5 个值 52793 的实例。这个值很受欢迎，因为 当前存储桶 (6) 和前一个存储桶 (1) 的端点编号之间的差值为 5。因此，在压缩之前，值 52793 是 5 个存储桶的端点。

以下注释显示了哪些存储桶被压缩，哪些值很受欢迎：

ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              1          52792 -> nonpopular
              6          52793 -> buckets 2-6 compressed into 6; popular
              8          52794 -> buckets 7-8 compressed into 8; popular
              9          52795 -> nonpopular
             10          52796 -> nonpopular
             12          52797 -> buckets 11-12 compressed into 12; popular
             14          52798 -> buckets 13-14 compressed into 14; popular
             23          52799 -> buckets 15-23 compressed into 23; popular

11.5 Frequency Histograms

在频率直方图中，每个不同的列值对应于直方图的一个桶。 因为每个值都有自己的专用桶，所以一些桶可能有很多值(相同的值)，而另一些则很少。

频率直方图的类比是对硬币进行分类，以便每个硬币最初都有自己的桶。 例如，第一个便士在桶 1 中，第二个便士在桶 2 中，第一个镍币在桶 3 中，依此类推。 然后，您将所有便士合并到一个便士桶中，将所有镍币合并到一个镍桶中，以此类推，剩下的硬币。

补充知识：penny（便士），nickel（镍币）， dime， quarter都是硬币，面值分别为1,5，10,25美分。

11.5.1 Criteria For Frequency Histograms

频率直方图取决于请求的直方图桶的数量。

如“Oracle数据库如何选择直方图类型”中的逻辑图所示，数据库在满足以下条件时创建频率直方图：

• NDV 小于或等于 n，其中 n 是直方图桶的数量（默认为 254）。
  例如，sh.countries.country_subregion_id 列有 8 个不同的值，范围从 52792 到 52799。如果 n 是默认值 254，则优化器会创建频率直方图，因为 8 <= 254。

• DBMS_STATS 统计信息收集过程中的estimate_percent 参数设置为用户指定的值或AUTO_SAMPLE_SIZE。

从 Oracle Database 12c 开始，如果采样大小是默认值 AUTO_SAMPLE_SIZE，则数据库会根据全表扫描创建频率直方图。对于所有其他采样百分比规范，数据库从样本中得出频率直方图。在 Oracle Database 12c 之前的版本中，数据库基于小样本收集直方图，这意味着低频值通常不会出现在样本中。在这种情况下使用密度有时会导致优化器高估选择性。

11.5.2 Generating a Frequency Histogram

此场景显示如何使用示例模式生成频率直方图。

假设：此方案假定您要在 sh.countries.country_subregion_id 列上生成频率直方图。 该表有 23 行。

以下查询显示 country_subregion_id 列包含 8 个不均匀分布的不同值（包括样本输出）：

SELECT country_subregion_id, count(*)
FROM   sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
 
COUNTRY_SUBREGION_ID   COUNT(*)
-------------------- ----------
               52792          1
               52793          5
               52794          2
               52795          1
               52796          1
               52797          2
               52798          2
               52799          9

收集 sh.countries 和 country_subregion_id 列的统计信息，让桶的数量默认为 254。

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS ( 
    ownname    => 'SH'
,   tabname    => 'COUNTRIES'
,   method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID'
);
END;

查询 country_subregion_id 列的直方图信息。优化器选择频率直方图，因为列中存在 n 个或更少的不同值，其中 n 默认为 254。

SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME='COUNTRIES'
AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
 
TABLE_NAME COLUMN_NAME          NUM_DISTINCT HISTOGRAM
---------- -------------------- ------------ ---------------
COUNTRIES  COUNTRY_SUBREGION_ID            8 FREQUENCY

查询 country_subregion_id 列的端点编号和端点值。可以看到，bucket进行了压缩。

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM   USER_HISTOGRAMS
WHERE  TABLE_NAME='COUNTRIES'
AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
 
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              1          52792
              6          52793
              8          52794
              9          52795
             10          52796
             12          52797
             14          52798
             23          52799

图 11-2 是直方图中 8 个桶的图形说明。 每个值都表示为放入桶中的硬币。

如图 11-2 所示，每个不同的值都有自己的桶。 因为这是一个频率直方图，所以端点编号是端点的累积频率。 对于 52793，端点编号 6 表示该值出现 5 次 (6 - 1)。 对于 52794，端点编号 8 表示该值出现 2 次 (8 - 6)。

每个端点至少比前一个端点大 2 的存储桶都包含一个流行值。 因此，桶 6、8、12、14 和 23 包含流行值。 优化器根据端点数计算它们的基数。 例如，优化器使用以下公式计算值 52799 的基数 ©，其中表中的行数为 23：

C = 23 * ( 9 / 23 )

存储桶 1、9 和 10 包含非流行值。 优化器根据密度估计它们的基数。

11.6 Top Frequency Histograms

最高频率直方图是频率直方图的一种变体，它忽略统计上不显著的非流行值。

例如，如果一堆 1000 枚硬币只包含一个便士，那么在将硬币分类到桶中时，您可以忽略该便士。 最高频率直方图可以为高度流行的值生成更好的直方图。

11.6.1 Criteria For Top Frequency Histograms

如果少量值占据了大部分行，那么即使 NDV 大于请求的直方图桶的数量，在这小组值上创建频率直方图也是有用的。 为了为流行值创建质量更好的直方图，优化器会忽略非流行值并创建最高频率直方图。

如《Oracle 数据库如何选择直方图类型》中的逻辑图所示，数据库在满足以下条件时创建最高频率直方图：

• NDV 大于 n，其中 n 是直方图桶的数量（默认为 254）。
• 前n个频繁值所占行的百分比等于或大于阈值p，其中p为(1-(1/n))*100。
• DBMS_STATS 统计信息收集过程中的estimate_percent 参数设置为AUTO_SAMPLE_SIZE。

11.6.2 Generating a Top Frequency Histogram

此场景显示如何使用示例模式生成最高频率直方图。

假设：此方案假设您要在 sh.countries.country_subregion_id 列上生成最高频率直方图。 该表有 23 行。

以下查询显示 country_subregion_id 列包含 8 个不均匀分布的不同值（包括样本输出）：

SELECT country_subregion_id, count(*)
FROM   sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
 
COUNTRY_SUBREGION_ID   COUNT(*)
-------------------- ----------
               52792          1
               52793          5
               52794          2
               52795          1
               52796          1
               52797          2
               52798          2
               52799          9

收集 sh.countries 和 country_subregion_id 列的统计信息，指定的桶数少于不同的值(7<8)。

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (
    ownname    => 'SH'
,   tabname    => 'COUNTRIES'
,   method_opt => 'FOR COLUMNS COUNTRY_SUBREGION_ID SIZE 7'
);
END;

查询 country_subregion_id 列的直方图信息(NUM_DISTINCT和原文中的7不同)。

SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME='COUNTRIES'
AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
 
TABLE_NAME COLUMN_NAME          NUM_DISTINCT HISTOGRAM
---------- -------------------- ------------ ---------------
COUNTRIES  COUNTRY_SUBREGION_ID            8 TOP-FREQUENCY

sh.countries.country_subregion_id 列包含 8 个不同的值，但直方图仅包含 7 个桶，使得 n=7。 在这种情况下，数据库只能创建最高频率或混合直方图。 在 country_subregion_id 列中，最频繁的前 7 个值占据了 95.6% （22/23）的行，超过了 85.7% （6/7）的阈值，生成了最高频率直方图。

查询该列的端点编号和端点值。

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM   USER_HISTOGRAMS
WHERE  TABLE_NAME='COUNTRIES'
AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
 
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              1          52792
              6          52793
              8          52794
              9          52796
             11          52797
             13          52798
             22          52799

和频率直方图非常相似，只是把非流行值52795去掉了。

图 11-3 是顶部频率直方图中 7 个桶的图形说明。 这些值在图中表示为硬币。

如图 11-3 所示，每个不同的值都有自己的桶，除了 52795，因为它不受欢迎且统计上不显著，所以从直方图中排除。 与标准频率直方图一样，端点编号表示值的累积频率。

11.7 Height-Balanced Histograms (Legacy)

在传统的高度平衡直方图中，列值被分成桶，以便每个桶包含大约相同数量的行。

例如，如果您有 99 个硬币要分配到 4 个桶中，则每个桶包含大约 25 个硬币。 直方图显示了端点在值范围内的位置。

11.7.1 Criteria for Height-Balanced Histograms

在 Oracle Database 12c 之前，数据库会在 NDV 大于 n 时创建高度平衡直方图。 这种类型的直方图对于范围谓词和相等谓词非常有用，这些值在至少两个存储桶中显示为端点。

如“Oracle数据库如何选择直方图类型”中的逻辑图所示，数据库在满足以下条件时创建高度平衡直方图：

• NDV 大于 n，其中 n 是直方图桶的数量（默认为 254）。
• DBMS_STATS 统计信息收集过程中的estimate_percent 参数未设置为AUTO_SAMPLE_SIZE。

因此，如果 Oracle Database 12c 创建了新的直方图，并且采样百分比为 AUTO_SAMPLE_SIZE，则直方图要么是最高频率，要么是混合的，但不是高度平衡的。

如果将 Oracle Database 11g 升级到 Oracle Database 12c，则在升级之前创建的任何基于高度的直方图都将继续使用。 但是，如果您刷新创建直方图的表上的统计信息，则数据库会替换该表上现有的高度平衡直方图。 替换直方图的类型取决于 NDV 和以下标准：

• 如果采样百分比为 AUTO_SAMPLE_SIZE，则数据库创建混合或频率直方图。
• 如果采样百分比不是 AUTO_SAMPLE_SIZE，则数据库创建高度平衡或频率直方图。

11.7.2 Generating a Height-Balanced Histogram

这个场景展示了如何使用示例模式生成高度平衡的直方图。

假设：此方案假设您要在 sh.countries.country_subregion_id 列上生成高度平衡的直方图。 该表有 23 行。

以下查询显示 country_subregion_id 列包含 8 个不均匀分布的不同值：

SELECT country_subregion_id, count(*)
FROM   sh.countries
GROUP BY country_subregion_id
ORDER BY 1;
 
COUNTRY_SUBREGION_ID   COUNT(*)
-------------------- ----------
               52792          1
               52793          5
               52794          2
               52795          1
               52796          1
               52797          2
               52798          2
               52799          9

收集 sh.countries 和 country_subregion_id 列的统计信息，指定的桶数少于不同的值。
注意：要模拟创建基于高度的直方图所必需的 Oracle Database 11g 行为，请将estimate_percent 设置为非默认值。 如果您指定非默认百分比，则数据库会创建频率或高度平衡直方图。

BEGIN  DBMS_STATS.GATHER_TABLE_STATS ( 
    ownname          => 'SH'
,   tabname          => 'COUNTRIES'
,   method_opt       => 'FOR COLUMNS COUNTRY_SUBREGION_ID SIZE 7'
,   estimate_percent => 100 
);
END;

查询 country_subregion_id 列的直方图信息。

SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME='COUNTRIES'
AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
 
TABLE_NAME COLUMN_NAME          NUM_DISTINCT HISTOGRAM
---------- -------------------- ------------ ---------------
COUNTRIES  COUNTRY_SUBREGION_ID            8 HEIGHT BALANCED

优化器选择高度平衡的直方图，因为不同值的数量 (8) 大于桶的数量 (7)，并且 estimate_percent 值是非默认值。

查询每个不同值占用的行数。

SELECT COUNT(country_subregion_id) AS NUM_OF_ROWS, country_subregion_id 
FROM   countries 
GROUP BY country_subregion_id 
ORDER BY 2;
 
NUM_OF_ROWS COUNTRY_SUBREGION_ID
----------- --------------------
          1                52792
          5                52793
          2                52794
          1                52795
          1                52796
          2                52797
          2                52798
          9                52799

查询 country_subregion_id 列的端点编号和端点值。

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE
FROM   USER_HISTOGRAMS
WHERE  TABLE_NAME='COUNTRIES'
AND    COLUMN_NAME='COUNTRY_SUBREGION_ID';
 
ENDPOINT_NUMBER ENDPOINT_VALUE
--------------- --------------
              0          52792
              2          52793
              3          52795
              4          52798
              7          52799

下图表示高度平衡的直方图。 这些值在图中表示为硬币。

存储桶编号与端点编号相同。 优化器将每个桶的最后一行的值记录为端点值，然后检查确保最小值是第一个桶的端点值，最大值是最后一个桶的端点值。 在此示例中，优化器添加存储桶 0，以便最小值 52792 是存储桶的端点。
优化器必须将 23 行平均分配到 7 个指定的直方图桶中，因此每个桶包含大约 3 行。 但是，优化器会压缩具有相同端点的存储桶。 因此，不是桶 1 包含 2 个值 52793 的实例，而桶 2 包含 3 个值 52793 的实例，优化器将所有 5 个值 52793 的实例放入桶 2。类似地，桶 5、6 和 7 不是包含 3 每个值，每个桶的端点为 52799，优化器将值 52799 的所有 9 个实例放入桶 7。
在此示例中，存储桶 3 和 4 包含非流行值，因为当前端点编号与先前端点编号之间的差值为 1。优化器根据密度计算这些值的基数。 其余的桶包含流行的值。 优化器根据端点编号计算这些值的基数。

11.8 Hybrid Histograms

混合直方图结合了基于高度的直方图和频率直方图的特征。 这种“两全其美”的方法使优化器能够在某些情况下获得更好的选择性估计。

基于高度的直方图有时会对几乎流行的值产生不准确的估计。 例如，只在一个桶作为端点值出现，但几乎占据两个桶的值不被认为是流行的。

为了解决这个问题，混合直方图分配值使得没有一个值占用超过一个桶，然后为直方图中的每个端点（桶）存储端点重复计数值，即端点值重复的次数 . 通过使用重复计数，优化器可以获得几乎流行值的准确估计。

11.8.1 How Endpoint Repeat Counts Work

分发在桶中的硬币的类比说明了显示端点重复计数的工作。

下图说明了将值从低到高排序的硬币列。

您收集此表的统计信息，将 DBMS_STATS.GATHER_TABLE_STATS 的 method_opt 参数设置为 FOR ALL COLUMNS SIZE 3。在这种情况下，优化器最初将硬币列中的值分组到三个桶中，如下图所示。

如果存储桶边界拆分一个值，使得该值的某些出现在一个存储桶中，而一些在另一个存储桶中，则优化器将存储桶边界（以及所有其他随后的存储桶边界）向前移动以包括该值的所有出现。 例如，优化器移动值 5，使其现在完全位于第一个存储桶中，而值 25 现在完全位于第二个存储桶中。

端点重复计数测量相应存储桶端点（即右存储桶边界处的值）自身重复的次数。 例如，在第一个桶中，值 5 重复了 3 次，因此端点重复计数为 3。

高度平衡直方图存储的信息不如混合直方图多。 通过使用重复计数，优化器可以准确地确定端点值的出现次数。 例如，优化器知道值 5 出现 3 次，值 25 出现 4 次，值 100 出现 2 次。 此频率信息有助于优化器生成更好的基数估计。

11.8.2 Criteria for Hybrid Histograms

与最高频率直方图相比，混合直方图的唯一区别标准是最高 n 个频率值小于内部阈值 p。
如“Oracle数据库如何选择直方图类型”中的逻辑图所示，数据库在满足以下条件时创建混合直方图：

• NDV 大于 n，其中 n 是直方图桶的数量（默认为 254）。
• 最高频率直方图的标准不适用。
  即前 n 个频繁值占用的行的百分比小于阈值 p，其中 p 为 (1-(1/n))*100。
• DBMS_STATS 统计信息收集过程中的estimate_percent 参数设置为AUTO_SAMPLE_SIZE。
• 如果用户指定他们自己的百分比，那么数据库会创建频率或高度平衡的直方图。

11.8.3 Generating a Hybrid Histogram

此场景显示如何使用示例模式生成混合直方图。

假设：此方案假定您要在 sh.products.prod_subcategory_id 列上生成混合直方图。 该表有 72 行。 prod_subcategory_id 列包含 22 个不同的值。

收集 sh.products 和 prod_subcategory_id 列的统计信息，指定 10 个存储桶。

BEGIN  DBMS_STATS.GATHER_TABLE_STATS ( 
    ownname     => 'SH'
,   tabname     => 'PRODUCTS'
,   method_opt  => 'FOR COLUMNS PROD_SUBCATEGORY_ID SIZE 10'
);
END;

查询每个不同值占用的行数。

SELECT COUNT(prod_subcategory_id) AS NUM_OF_ROWS, prod_subcategory_id
FROM   products
GROUP BY prod_subcategory_id
ORDER BY 1 DESC;
 
NUM_OF_ROWS PROD_SUBCATEGORY_ID
----------- -------------------
          8                2014
          7                2055
          6                2032
          6                2054
          5                2056
          5                2031
          5                2042
          5                2051
          4                2036
          3                2043
          2                2033
          2                2034
          2                2013
          2                2012
          2                2053
          2                2035
          1                2022
          1                2041
          1                2044
          1                2011
          1                2021
          1                2052
 
22 rows selected.

SELECT COUNT(prod_subcategory_id) AS NUM_OF_ROWS, prod_subcategory_id
FROM   products
GROUP BY prod_subcategory_id
ORDER BY 2;

NUM_OF_ROWS PROD_SUBCATEGORY_ID
----------- -------------------
          1                2011
          2                2012
          2                2013
          8                2014
          1                2021
          1                2022
          5                2031
          6                2032
          2                2033
          2                2034
          2                2035
          4                2036
          1                2041
          5                2042
          3                2043
          1                2044
          5                2051
          1                2052
          2                2053
          6                2054
          7                2055
          5                2056

22 rows selected. 

该列包含 22 个不同的值。 因为桶数 (10) 少于 22，优化器无法创建频率直方图。 优化器同时考虑混合直方图和最高频率直方图。 要获得最高频率直方图的资格，前 10 个最频繁值占据的行的百分比必须等于或大于阈值 p，其中 p 为 (1-(1/10))*100，或 90%。 但是，在这种情况下，前 10 个最频繁的值占据了 72 行中的 54 行，仅占总数的 75%。 因此，优化器选择混合直方图，因为最高频率直方图的标准不适用。

查询 country_subregion_id 列的直方图信息。

SELECT TABLE_NAME, COLUMN_NAME, NUM_DISTINCT, HISTOGRAM
FROM   USER_TAB_COL_STATISTICS
WHERE  TABLE_NAME='PRODUCTS'
AND    COLUMN_NAME='PROD_SUBCATEGORY_ID';

TABLE_NAME COLUMN_NAME         NUM_DISTINCT HISTOGRAM
---------- ------------------- ------------ ---------
PRODUCTS   PROD_SUBCATEGORY_ID 22           HYBRID

查询 country_subregion_id 列的端点编号、端点值和端点重复计数，端点重复计数是本直方图类型独有的。

SELECT ENDPOINT_NUMBER, ENDPOINT_VALUE, ENDPOINT_REPEAT_COUNT
FROM   USER_HISTOGRAMS
WHERE  TABLE_NAME='PRODUCTS'
AND    COLUMN_NAME='PROD_SUBCATEGORY_ID'
ORDER BY 1;
 
ENDPOINT_NUMBER ENDPOINT_VALUE ENDPOINT_REPEAT_COUNT
--------------- -------------- ---------------------
              1           2011                     1
             13           2014                     8
             26           2032                     6
             36           2036                     4
             45           2043                     3
             51           2051                     5
             52           2052                     1
             54           2053                     2
             60           2054                     6
             72           2056                     5
 
10 rows selected.

端点重复计数显示重复存储桶中最高值的次数。 通过使用这些值的端点编号和重复计数，优化器可以估计基数。 例如，桶 36 包含值 2033、2034、2035 和 2036 的实例。端点值 2036 的端点重复计数为 4，因此优化器知道存在该值的 4 个实例。 对于 2033 等不是端点的值，优化器使用密度估计基数。