简答题
5. 什么是直方图(Histogram)?
【正确答案】直方图是CBO中的一个重点，也是一个难点部分，在面试中常常被问到。
(1)直方图的意义 在Oracle数据库中，CBO会默认认为目标列的数据在其最小值(LOW_VALUE)和最大值(HIGH_VALUE)之间是均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加WHERE查询条件后的可选择率以及结果集的Cardinality，进而据此来计算成本值并选择执行计划，但目标列的数据是均匀分布这个原则并不总是正确的，一些目标列的数据分布是不均匀的，极度倾斜的。对这样的列如果还按照均匀分布的原则去计算可选择率与Cardinality，并据此来计算成本、选择执行计划，那么CBO所选择的执行计划就可能是不合理的，甚至是错误的，所以此时应该收集列的直方图。
直方图是一种列的特殊的统计信息，主要用来描述列上的数据分布情况。当数据分布倾斜时，直方图可以有效地提升Cardinality评估的准确度。构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划。如果对目标列收集了直方图，那么意味着CBO将不再认为该目标列上的数据是均匀分布的了，CBO就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的Cardinality，进而据此计算成本并选择相应的执行计划。
直方图实际上存储在数据字典基表SYS.HISTGRM$中，可以通过数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS来分别查看表、分区表的分区和分区表的子分区的直方图统计信息。
(2)直方图的使用场合 通常情况下在以下场合中建议使用直方图：
1)当WHERE子句引用了列值分布存在明显偏差的列时。需要注意的是，若查询不引用该列，则在该列上创建直方图没有意义。
2)当列值导致不正确的判断时，这种情况通常会发生在多表连接时。如优化器对中间结果集的大小做出不正确的判断，则它可能会选择一种未达到最优化的表连接方法。因此向该列添加直方图经常会向优化器提供使用最佳连接方法所需的信息。
(3)直方图的分类 Oracle数据库里的直方图使用了一种称为Bucket(桶)的方式来描述目标列的数据分布。Bucket(桶)是一个逻辑上的概念，相当于分组，每个Bucket就是一组，每个Bucket里会存储一个或多个目标列中的数据。Oracle会用两个维度来描述一个Bucket，这两个维度分别是ENDPOINT_NUMBER和ENDPOINT_VALUE，Oracle会将每个Bucket的这两个维度记录在数据字典基表SYS.HISTGRM$中。列的直方图的类型可以通过查询视图DBA_TAB_COL_STATISTICS的HISTOGRAM列来获取，一般情况下包含3类，NONE(没有直方图)、FREQUENCY(频率直方图，也称为等频直方图)、HEIGHTBALANCED(高度平衡直方图，也称为等高直方图)。在Oracle 12c中，又新增了两种类型的直方图，分别是顶级频率直方图(Top Frequency Histogram)和混合直方图(Hybrid Histogram)，本书只讨论频率和高度平衡直方图。
1)频率(Frequency，Freq)直方图。如果存储在数据字典里描述目标列直方图的Bucket的数量等于目标列的DISTINCT值的数量，那么这种类型的直方图就是频率(Frequency)直方图。频率直方图只适用于那些目标列的DISTINCT值数量小于或等于254的情形。需要注意的是，在Oracle 12c中频率直方图所对应的Bucket的数量可以超过254。
对于频率直方图而言，目标列有多少个DISTINCT值，数据字典基表SYS.HISTGRM$中就会存储多少条记录，每一条记录就代表了对其中的一个Bucket的描述。在DBA_TAB_HISTOGRAMS视图中，字段ENDPOINT_VALUE记录了DISTINCT值，而字段ENDPOINT_NUMBER则记录了到此DISTINCT值为止总共有多少条记录。需要注意的是，对频率直方图而言，ENDPOINT_NUMBER是一个累加值，可以用一条记录的ENDPOINT_NUMBER值减去它上一条记录的ENDPOINT_NUMBER值来得到这条记录本身所对应的ENDPOINT_VALUE值的记录数，SQL如下：
2)高度平衡(Height Balanced，HtBal)直方图。如果存储在数据字典里描述目标列直方图的Bucket的数量小于目标列的DISTINCT值的数量，那么这种类型的直方图就是高度平衡(Height Balanced)直方图。在高度平衡直方图中，执行计划的列的选择性没有频率直方图精确，而在现实很多时候，列的唯一值是超过254的，那么只能使用高度平衡直方图。在高度平衡直方图中，在DBA_TAB_HISTOGRAMS视图中，EDNPOINT_NUMBER代表桶号，且自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值。ENDPOINT_VALUE表示每一个桶中的最大值，而第一个桶记录的是最小值(Bucket为0的行，即EDNPOINT_NUMBER为0的行)。重复出现为ENDPOINT_VALUE的值称为Popular Value。若Popular Value所在记录的ENDPOINT_NUMBER值和它上一条记录的ENDPOINT_NUMBER值之间的差值越大，则意味着该Popular Value在目标表中所占的比例也就越大，它所对应的Cardinality也就越大。在高度平衡直方图中，除了最后1个桶可能包含的数据比其他的桶少以外，所有其他的桶包含相同数量的值，其值为目标表总记录数除以Bucket的数量。
3)频率和高度平衡直方图的比对，见表。
(4)收集直方图信息默认情况下，数据库会为列收集基本统汁信息，但不会收集直方图信息。Oracle通过指定DBMS_STATS的METHOD_OPT参数来创建直方图。METHOD_OPT参数可以有如下的输入值：
其中的size_clause必须符合如下的格式：
含义如下所示：
I)SKEWONLY：只对数据分布不均衡的列收集直方图统计信息。
2)REPEAT：只对已经有直方图统计信息的列收集直方图统计信息。
3)AUTO：让Oracle自行决定是否对目标列收集直方图统计信息，以及使用哪种类型的直方图。
4)integer：直方图的Bucket的数量，必须是在1～254的范围内，1表示删除该目标列上的直方图统计信息。
METHOD_OPT参数的默认值为“FORALL COLUMNS SIZE AUTO”，“FOR ALL COLUMNS SIZE 1”表示删除所有列直方图统计信息。下面是一些常用的收集方法：
1)对T表上所有有索引的列以自动收集的方式收集直方图：FOR ALL INDEXED COLUMNS SIZE AUTO。
2)对T表上的列A和列B以自动收集的方式收集直方图：FOR COLUMNS SIZEAUTO A B。
3)对T表上的列A和列B收集直方图统计信息，同时指定BUCKET数量均为10：FOR COLUMNSSIZE 10 A B。
4)对T表上的列A和列B收集直方图统计信息，同时指定列A的BUCKET数量为10，列B的BUCKET数量为5：FOR COLUMNS A SIZE 10 B SIZE 5。
5)只删除表T上列A的直方图统计信息：FOR COLUMNS A SIZE 1。
6)删除表T上所有列的直方图统计信息：FOR ALL COLUMNS SIZE 1。
如果需要删除某个列SAL的直方图信息，在Oracle 10g中可以通过设置“METHOD_OPT=＞'FOR COLUMNS SAL SIZE 1'”，但这却得再次收集表的统计信息，十分不合理，所以，在Oracle 11g中，有如下方法可以直接删除直方图信息：
(1)直方图的意义 在Oracle数据库中，CBO会默认认为目标列的数据在其最小值(LOW_VALUE)和最大值(HIGH_VALUE)之间是均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加WHERE查询条件后的可选择率以及结果集的Cardinality，进而据此来计算成本值并选择执行计划，但目标列的数据是均匀分布这个原则并不总是正确的，一些目标列的数据分布是不均匀的，极度倾斜的。对这样的列如果还按照均匀分布的原则去计算可选择率与Cardinality，并据此来计算成本、选择执行计划，那么CBO所选择的执行计划就可能是不合理的，甚至是错误的，所以此时应该收集列的直方图。
直方图是一种列的特殊的统计信息，主要用来描述列上的数据分布情况。当数据分布倾斜时，直方图可以有效地提升Cardinality评估的准确度。构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划。如果对目标列收集了直方图，那么意味着CBO将不再认为该目标列上的数据是均匀分布的了，CBO就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的Cardinality，进而据此计算成本并选择相应的执行计划。
直方图实际上存储在数据字典基表SYS.HISTGRM$中，可以通过数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS来分别查看表、分区表的分区和分区表的子分区的直方图统计信息。
(2)直方图的使用场合 通常情况下在以下场合中建议使用直方图：
1)当WHERE子句引用了列值分布存在明显偏差的列时。需要注意的是，若查询不引用该列，则在该列上创建直方图没有意义。
2)当列值导致不正确的判断时，这种情况通常会发生在多表连接时。如优化器对中间结果集的大小做出不正确的判断，则它可能会选择一种未达到最优化的表连接方法。因此向该列添加直方图经常会向优化器提供使用最佳连接方法所需的信息。
(3)直方图的分类 Oracle数据库里的直方图使用了一种称为Bucket(桶)的方式来描述目标列的数据分布。Bucket(桶)是一个逻辑上的概念，相当于分组，每个Bucket就是一组，每个Bucket里会存储一个或多个目标列中的数据。Oracle会用两个维度来描述一个Bucket，这两个维度分别是ENDPOINT_NUMBER和ENDPOINT_VALUE，Oracle会将每个Bucket的这两个维度记录在数据字典基表SYS.HISTGRM$中。列的直方图的类型可以通过查询视图DBA_TAB_COL_STATISTICS的HISTOGRAM列来获取，一般情况下包含3类，NONE(没有直方图)、FREQUENCY(频率直方图，也称为等频直方图)、HEIGHTBALANCED(高度平衡直方图，也称为等高直方图)。在Oracle 12c中，又新增了两种类型的直方图，分别是顶级频率直方图(Top Frequency Histogram)和混合直方图(Hybrid Histogram)，本书只讨论频率和高度平衡直方图。
1)频率(Frequency，Freq)直方图。如果存储在数据字典里描述目标列直方图的Bucket的数量等于目标列的DISTINCT值的数量，那么这种类型的直方图就是频率(Frequency)直方图。频率直方图只适用于那些目标列的DISTINCT值数量小于或等于254的情形。需要注意的是，在Oracle 12c中频率直方图所对应的Bucket的数量可以超过254。
对于频率直方图而言，目标列有多少个DISTINCT值，数据字典基表SYS.HISTGRM$中就会存储多少条记录，每一条记录就代表了对其中的一个Bucket的描述。在DBA_TAB_HISTOGRAMS视图中，字段ENDPOINT_VALUE记录了DISTINCT值，而字段ENDPOINT_NUMBER则记录了到此DISTINCT值为止总共有多少条记录。需要注意的是，对频率直方图而言，ENDPOINT_NUMBER是一个累加值，可以用一条记录的ENDPOINT_NUMBER值减去它上一条记录的ENDPOINT_NUMBER值来得到这条记录本身所对应的ENDPOINT_VALUE值的记录数，SQL如下：
2)高度平衡(Height Balanced，HtBal)直方图。如果存储在数据字典里描述目标列直方图的Bucket的数量小于目标列的DISTINCT值的数量，那么这种类型的直方图就是高度平衡(Height Balanced)直方图。在高度平衡直方图中，执行计划的列的选择性没有频率直方图精确，而在现实很多时候，列的唯一值是超过254的，那么只能使用高度平衡直方图。在高度平衡直方图中，在DBA_TAB_HISTOGRAMS视图中，EDNPOINT_NUMBER代表桶号，且自动省去EDNPOINT_VALUE值相同且ENDPOINT_NUMBER相邻的桶的值。ENDPOINT_VALUE表示每一个桶中的最大值，而第一个桶记录的是最小值(Bucket为0的行，即EDNPOINT_NUMBER为0的行)。重复出现为ENDPOINT_VALUE的值称为Popular Value。若Popular Value所在记录的ENDPOINT_NUMBER值和它上一条记录的ENDPOINT_NUMBER值之间的差值越大，则意味着该Popular Value在目标表中所占的比例也就越大，它所对应的Cardinality也就越大。在高度平衡直方图中，除了最后1个桶可能包含的数据比其他的桶少以外，所有其他的桶包含相同数量的值，其值为目标表总记录数除以Bucket的数量。
3)频率和高度平衡直方图的比对，见表。
(4)收集直方图信息默认情况下，数据库会为列收集基本统汁信息，但不会收集直方图信息。Oracle通过指定DBMS_STATS的METHOD_OPT参数来创建直方图。METHOD_OPT参数可以有如下的输入值：
其中的size_clause必须符合如下的格式：
含义如下所示：
I)SKEWONLY：只对数据分布不均衡的列收集直方图统计信息。
2)REPEAT：只对已经有直方图统计信息的列收集直方图统计信息。
3)AUTO：让Oracle自行决定是否对目标列收集直方图统计信息，以及使用哪种类型的直方图。
4)integer：直方图的Bucket的数量，必须是在1～254的范围内，1表示删除该目标列上的直方图统计信息。
METHOD_OPT参数的默认值为“FORALL COLUMNS SIZE AUTO”，“FOR ALL COLUMNS SIZE 1”表示删除所有列直方图统计信息。下面是一些常用的收集方法：
1)对T表上所有有索引的列以自动收集的方式收集直方图：FOR ALL INDEXED COLUMNS SIZE AUTO。
2)对T表上的列A和列B以自动收集的方式收集直方图：FOR COLUMNS SIZEAUTO A B。
3)对T表上的列A和列B收集直方图统计信息，同时指定BUCKET数量均为10：FOR COLUMNSSIZE 10 A B。
4)对T表上的列A和列B收集直方图统计信息，同时指定列A的BUCKET数量为10，列B的BUCKET数量为5：FOR COLUMNS A SIZE 10 B SIZE 5。
5)只删除表T上列A的直方图统计信息：FOR COLUMNS A SIZE 1。
6)删除表T上所有列的直方图统计信息：FOR ALL COLUMNS SIZE 1。
如果需要删除某个列SAL的直方图信息，在Oracle 10g中可以通过设置“METHOD_OPT=＞'FOR COLUMNS SAL SIZE 1'”，但这却得再次收集表的统计信息，十分不合理，所以，在Oracle 11g中，有如下方法可以直接删除直方图信息：
【答案解析】