1 直方图的含义
在Oracle数据库中，CBO会默认认为目标列的数据在其最小值LOW_VALUE和最大值HIGH_VALUE之间均匀分布的，并且会按照这个均匀分布原则来计算对目标列施加查询条件后的可选择率以及结果集的Cardinality，进而据此来计算成本值并选择执行计划。但目标列的数据是均匀分布这个原则并不总是正确的，在实际的系统中，我们很容易就能看到一些目标列的数据分布是不均匀的，甚至是极度倾斜、分布极度不均衡的。对这样的列如果还按照均匀分布的原则去计算可选择率与Cardinality，并据此来计算成本、选择执行计划，那么CBO所选择的执行计划就可能是不合理的，甚至是错误的。
看一个由于数据分布极不均衡而导致CBO选错执行计划的例子：
zx@ORCL>createtablet1(anumber(5),bvarchar2(5)); Tablecreated. zx@ORCL>declarecntnumber(5):=1; 2begin 3loop 4insertintot1values(1,'1'); 5ifcnt=10000then 6exit; 7endif; 8cnt:=cnt+1; 9endloop; 10insertintot1values(2,'2'); 11commit; 12end; 13/ PL/sqlproceduresuccessfullycompleted. zx@ORCL>selectb,count(*)fromt1groupbyb; BCOUNT(*) ------------------------- 110000 21 zx@ORCL>createindext1_ix_bont1(b); Indexcreated.
zx@ORCL>execdbms_stats.gather_table_stats(USER,'T1',estimate_percent=>100,method_opt=>'forallcolumnssize1'); PL/sqlproceduresuccessfullycompleted. zx@ORCL>select*fromt1whereb='2'; AB ------------------------- 22 zx@ORCL>select*fromtable(dbms_xplan.display_cursor(null,null,'all')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- sql_ID5p7b772tpcvm4,childnumber0 ------------------------------------- select*fromt1whereb='2' Planhashvalue:3617692013 -------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| -------------------------------------------------------------------------- |0|SELECTSTATEMENT||||7(100)|| |*1|TABLEACCESSFULL|T1|5001|25005|7(0)|00:00:01| -------------------------------------------------------------------------- .....省略部分输出
从执行计划可以看出执行计划走的是全表扫描，但是很显然应该走索引T1_IX_B。这是因为CBO默认认为列B的数据是均匀分布的，而列B上的distinct值只有1和2这两值，所以CBO评估出来的对列B施加等值查询条件的可选择率就是1/2,进而评估出来对列B施加等值查询条件的结果集的Cardinality就是5001：
zx@ORCL>selectround(10001*(1/2))fromdual; ROUND(10001*(1/2)) ------------------ 5001
正因为CBO评估出上述等值查询要返回结果集的Cardinality是5001，已经占了表T1总记录数的一半，所以CBO认为此时再走列B上的索引T1_IX_B就已经不合适了，进而就选择了对列T1的全表扫描。但实际上，CBO对上述等值查询要返回结果集的Cardinality的评估已经与事实严重不符，评估出来的值是5001，其实却只有1，差了好几个数量级。
CBO这里选择了执行计划，正确的执行计划应该是走索引T1_IX_B。CBO选错执行计划的根本原因是表T1的列B的分布实际上是极度不均衡的(列B一共就两值，其中10000个1，只有1个2)，CBO在评估的一开始所用的原则就错了，当然结果也就错了。
为了解决上述问题，Oracle引入了直方图(Histogram)。直方图是一种特殊的列统计信息，它详细描述了目标列的数据分布情况。直方图实际上存储在数据字典基表HISTGRM$中，可以通过数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS和DBA_SUBPART_HISTOGRAMS来分别查看表、分区表的分区和分区表的子分区的直方图统计信息。
如果对目标列收集了直方图，则意味着CBO将不再认为该目标列上的数据是均匀分布的了，CBO就会用该目标列上的直方图统计信息来计算对该列施加查询条件后的可选择率和返回结果集的Cardinality，进而据此计算成本并选择相应的执行计划。
还用上面的例子，对表T1的列B收集了直方图统计信息后，CBO正确地评估出了返回结果集的Cardinality不是5001而是1，进而就正确地选择了走索引T1_IX_B的执行计划：
zx@ORCL>execdbms_stats.gather_table_stats(USER,method_opt=>'forallcolumnssizeauto',cascade=>true); PL/sqlproceduresuccessfullycompleted. #清空shared_pool,生产系统不要随便执行 zx@ORCL>altersystemflushshared_pool; Systemaltered. zx@ORCL>select*fromt1whereb='2'; AB ------------------------- 22 zx@ORCL>select*fromtable(dbms_xplan.display_cursor(null,childnumber0 ------------------------------------- select*fromt1whereb='2' Planhashvalue:3579362925 --------------------------------------------------------------------------------------- |Id|Operation|Name|Rows|Bytes|Cost(%cpu)|Time| --------------------------------------------------------------------------------------- |0|SELECTSTATEMENT||||2(100)|| |1|TABLEACCESSBYINDEXROWID|T1|1|5|2(0)|00:00:01| |*2|INDEXRANGESCAN|T1_IX_B|1||1(0)|00:00:01| --------------------------------------------------------------------------------------- .....省略部分输出
所以，直方图就是专门为了准确评估这种分布不均匀的目标列的可选择率、结果集的Cardinality而被Oracle引入的，它详细描述了目标列的数据分布情况，并将这些分布情况记录在数据字典里，相当于直观地告诉了CBO这些列的数据分布情况，于是CBO就能据此来做出相对准确的判断。
2 直方图的类型
Oracle数据库里的直方图使用了一种称为Bucket(桶)的方式来描述目标列的数据分布。这有点类似哈希算法的Bucket，它实际上是一个逻辑上的概念，相当于分组，每个Bucket就是一组，每个Bucket里会存储一个或多个目标列上的数据。Oracle会用两个维度来描述一个Bucket，这两个维度分别是ENDPOINT NUMBER和ENDPOINT VALUE。Oracle会将每个Bucket的维度ENDPOIONTNUMBER和ENDPOINT VALUE记录在数据字典基表HISTGRM$中，这样就达到了目标列的直方图统计信息记录在数据字典中的目的。维度ENDPOINT NUMBER和ENDPOINT VALUE分别对应于数据字典DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS及DBA_SUBPART_HISTOGRAMS中的字段ENDPOINT_NUMBER/BUCKET_NUMBER和ENDPOINT_VALUE。同时，Oracle还会记录目标列的直方图统计信息所占用的Bucket的总数，可以通过数据字典DBA_TAB_COL_STATISTICS、DBA_PART_COL_STATISTICS及DBA_SUBPART_COL_STATISTICS中字段NUM_BUCKETS来查看目标列对应直方图的Bucket的总数。
在Oracle 12c之前，Oracle数据库里的直方图分为两种类型，分别是Frequency和HeightBalanced(Oracle 12c中还存在名为Top-Frequency和Hybrid类型的直方图)。在Oracle 12以之前，如果存储在数据字典里描述目标列直方图的Buckt的数量等于目标列的distinct值的数量，则这种类型的直方图就是Frequency类型的直方图。如果存储在数据字典里描述目标列直方图的Bucket的数量小于目标列的distinct值的数量，则这种类型的直方图就是Height Balanced类型的直方图。
2.1 Frequency类型的直方图
对于Frequency类型的直方图而言，目标列直方图的Bucket的数量就等于目标列的distinct的数量，此时目标列有多个个distinct值，Oracle在数据字典DBA_TAB_HISTOGRAMS、DBA-PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中就会存储多少条记录，每一条记录不代表了对其中的一个Bucket的描述，上述数据字典中的字段ENDPOINT_VALUE记录了这些distinct值，而字段ENDPOINT_NUMBER是一个累加值，实际上，我们可以用一条记录的ENDPOINT_NUMBER值减去它的上一条记录的ENDPOINT_NUMBER值来得到这条记录本身所对应的ENDPOINT_VALUE值的记录数。
实际上，Frequency类型的直方图就是把目标列的每一个distinct值都记录在数据字典里，同时在数据字典里记录记录每个distinct值在目标表里一共有多少条记录，这样CBO就能非常清楚地知道目标列在目标表里的实际数据分布情况了。这种Frequency类型的直方图所对应的收集方法并不适用于目标列的distinct值非常多的情形，所以Oracle对Frequence类型的直方图有如下限制：Frequency类型的直方图所对应的Bucket的数量不能超过254(注意，Oracle 12c 中将不再有这一限制，在Oracle 12c中Frequency类型的直方图所对应的Bucket的数量可以超过254)，即Frequency类型的直方图只适用于那些目标列的distinct值数量小于或等于254的情形。
zx@ORCL>createtableh(xnumber); Tablecreated. zx@ORCL>declare inumber; begin foriin1..3296loop insertintohvalues(1); 56endloop; 7foriin1..100loop 8insertintohvalues(3); 9endloop; 10foriin1..798loop 11insertintohvalues(5); 12endloop; 13foriin1..3970loop 14insertintohvalues(7); 15endloop; 16foriin1..16293loop 17insertintohvalues(10); 18endloop; 19foriin1..3399loop 20insertintohvalues(16); 21endloop; 22foriin1..3651loop 23insertintohvalues(27); 24endloop; 25foriin1..3892loop 26insertintohvalues(32); 27endloop; 28foriin1..3521loop 29insertintohvalues(39); 30endloop; 31foriin1..1080loop 32insertintohvalues(49); 33endloop; 34commit; 35end; 36/ PL/sqlproceduresuccessfullycompleted. zx@ORCL>selectcount(*)fromh; COUNT(*) ---------- 40000
按照Frequency类型直方图的定义，如果对列X收集Frequency类型的直方图，则DBA_TAB_HISTOGRAMS中应该有10条记录，而且这10条记录的ENDPOINT_VALUE记录的就是这10个distinct值，对应的ENDPOINT_NUMBER就是到此distinct值为止累加的行记录数。这10条记录的ENDPOINT_VALUE和ENDPOINT_NUMBER实际上可以用如下sql的显示结果来模拟：
zx@ORCL>selectxasx,count(*)ascardinality,sum(count(*))over(orderbyxrangeunboundedpreceding)ascum_cardinalityfromhgroupbyx; XCARDINALITYCUM_CARDINALITY ------------------------------------ 132963296 31003396 57984194 739708164 101629324457 16339927856 27365131507 32389235399 39352138920 49108040000 10rowsselected.
上述查询结果中的列X就模拟了DBA_TAB_HISTOGRAMS中那10条记录的ENDPOINT_VALUE，列CUM_CARDINALITY就模拟了DBA_TAB_HISTOGRAMS中那10条记录的ENDPOINT_NUMBER。
对表h的列x来实际收集一下直方图统计信息
zx@ORCL>execdbms_stats.gather_table_stats(ownname=>USER,tabname=>'H',method_opt=>'forcolumnssizeautoX',cascade=>true,estimate_percent=>100); PL/sqlproceduresuccessfullycompleted.
收集完统计信息后发现DBA_TAB_COL_STATISTICS中列x所对应的字段HISTOGRAM的值为NONE，这表明现在列x上依然没有直方图统计信息：
zx@ORCL>selecttable_name,column_name,num_distinct,density,num_buckets,histogramfromdba_tab_col_statisticswheretable_name='H'; TABLE_NAMECOLUMN_NAMNUM_DISTINCTDENSITYNUM_BUCKETSHISTOGRAM -------------------------------------------------------------------------------------------------- HX10.11NONE
这种现象是正常的。因为Oracle在自动收集直方图统计信息时会秉承一个原则，那就是只对那些用过的列(即在sql语句where条件中出现过的列)收集直方图统计信息。Oracle会在表SYS.COL_USAGE$中记录各表中各列的使用情况，在自动收集直方图统计信息时Oracle会查询SYS.COL_USAGE$，如果发现其中没有目标列的使用记录，那就不会对目标列收集直方图统计信息。表H刚刚建立，还没有在sql语句的where条件中使用过列X，所以这里不会对列X收集直方图统计信息。
收集直方图的前提条件是：1.列上的数据分布不均匀，2.列在sql的where条件中被使用过
zx@ORCL>selectname,intcol#fromsys.col$whereobj#=(selectobject_idfromdba_objectswhereobject_name='H'); NAMEINTCOL# ---------------------------------------------------------------------------------------------------- X1 zx@ORCL>selectobj#,intcol#,equality_predsfromsys.col_usage$whereobj#=(selectobject_idfromdba_objectswhereobject_name='H'); norowsselected zx@ORCL>selectcount(*)fromhwherex=10; COUNT(*) ---------- 16293 zx@ORCL>selectobj#,equality_predsfromsys.col_usage$whereobj#=(selectobject_idfromdba_objectswhereobject_name='H'); OBJ#INTCOL#EQUALITY_PREDS ---------------------------------- 8876611
zx@ORCL>execdbms_stats.gather_table_stats(ownname=>USER,estimate_percent=>100); PL/sqlproceduresuccessfullycompleted. zx@ORCL>selecttable_name,histogramfromdba_tab_col_statisticswheretable_name='H'; TABLE_NAMECOLUMN_NAMNUM_DISTINCTDENSITYNUM_BUCKETSHISTOGRAM -------------------------------------------------------------------------------------------------- HX10.000012510FREQUENCY
另外DBA_TAB_COL_STATISTICS中列x所对应的字段HISTORAM的值已经由NONE变成了RREQUENCY，这说明现在列X上已经有了Frequency类型的直方图
可以从DBA_TAB_HISTOGRAMS中看到列x的Frequence类型的直方图的具体信息：
zx@ORCL>selecttable_name,endpoint_number,endpoint_valuefromdba_tab_histogramswheretable_name='H'; TABLE_NAMECOLUMN_NAMENDPOINT_NUMBERENDPOINT_VALUE ------------------------------------------------- HX32961 HX33963 HX41945 HX81647 HX2445710 HX2785616 HX3150727 HX3539932 HX3892039 HX4000049 10rowsselected.
从结果中可以看出，DBA_TAB_HISTOGRAMS中的10条记录与之前模拟出来的结果一模一样。
介绍完Frequency类型的直方图的含义，现在来讨论Oracle数据库里针对文本类型字段的直方图统计的先天缺陷了。
在Oracle数据库，如果针对文本开的字段收集直方图统计信息，则Oracle只会将该文本字段的文本值的头32个字节(Byte)给取出来(实际上只取头15个字节)，并将其转换成一个浮点数，然后就将这个浮点数作为其直方图统计信息存储在上述数据字典里。这种处理机制的先天身陷就在于，对于那些超过32个字节的文本型字段，只要其对应记录的文本值的头32个字节相同，Oracle在收集直方图统计信息时就会认为这引起记录在该字段的文本值是相同的，即使实际上它们并不相同。这种先天性缺陷会直接影响CBO对相关文本类型字段的可选择率及返回结果集的Cardinality的评估。
使用之前的测试表T1，其中列B为文本型字段
zx@ORCL>selectb,count(*)fromt1groupbyb; BCOUNT(*) ------------------------- 110000 21 zx@ORCL>selectcount(*)fromt1whereb='1'; COUNT(*) ---------- 10000 zx@ORCL>execdbms_stats.gather_table_stats(USER,method_opt=>'forcolumnssizeautoB'); PL/sqlproceduresuccessfullycompleted. zx@ORCL>selecttable_name,histogramfromdba_tab_col_statisticswheretable_name='T1'; TABLE_NAMECOLUMN_NAMNUM_DISTINCTDENSITYNUM_BUCKETSHISTOGRAM -------------------------------------------------------------------------------------------------- T1B2.0000499952FREQUENCY T1A2.51NONE
从DBA_TAB_HISTOGRAMS中查看列B的直方图具体信息
zx@ORCL>selecttable_name,endpoint_valuefromdba_tab_histogramswheretable_name='T1'; TABLE_NAMECOLUMN_NAMENDPOINT_NUMBERENDPOINT_VALUE ------------------------------------------------- T1B100002.5442E+35 T1B100012.5961E+35 T1A01 T1A12
从结果可以看到，由文本型的'1'和'2'转换而来的浮点数。
转换方法：
select dump('1',16)from dual;
将0x31右边补0一直补到15个字节的长度，再将其转换为十进制数：
zx@ORCL>selectdump('1',16)fromdual;
DUMP('1',16)
------------------------------------------------
Typ=96Len=1:31
zx@ORCL>selectto_number('310000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')fromdual;
TO_NUMBER('310000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
----------------------------------------------------------------------------
2.5442E+35
转换出的值与数据字典的数据一致。
再创建一个测试表T2，有一个长度为33字节的文本型字段B：
zx@ORCL>createtablet2(bvarchar2(33));
Tablecreated.
zx@ORCL>insertintot2values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1');
1rowcreated.
zx@ORCL>insertintot2values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2');
1rowcreated.
zx@ORCL>insertintot2values('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2');
1rowcreated.
zx@ORCL>commit;
Commitcomplete.
这三条记录的头32个字节均相同，均为32个a，但distinct值有两个
zx@ORCL>selectb,length(b)fromt2; BLENGTH(B) ------------------------------------------------------------------------------------------------------------- aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa133 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa233 aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa233 zx@ORCL>selectcount(distinct(b))fromt2; COUNT(DISTINCT(B)) ------------------ 2
使用一下列B，以让SYS.COL_USAGE$中有列B的使用记录：
select count(*) from t2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2';
对列B以自动方式收集直方图：
zx@ORCL>execdbms_stats.gather_table_stats(USER,'T2',method_opt=>'forcolumnssizeautoB'); PL/sqlproceduresuccessfullycompleted.
现在DBA_TAB_COL_STATISTICS中列B所对应的字段HISTOGRAM的值为FREQUENCY(注意：10.2.0.4和11.2.0.1为FREQUENCY,11.2.0.4为HEIGHT BALANCED)，说明现在列B上已经有了Frequency类型的直方图统计信息：
sql>selecttable_name,histogramfromdba_tab_col_statisticswheretable_name='T2'; TABLE_NAMECOLUMN_NAMNUM_DISTINCTDENSITYNUM_BUCKETSHISTOGRAM -------------------------------------------------------------------------------------------------- T2 B 1.166666667 1FREQUENCY
注意，上述查询结果中文本型字段B的不同distinct的值只有1个，Frequency类型的直方图所在的Bucket数量也只有1个，这明显和事实不符。其实这已经说明了对那些超过32字节的文本型字段而言，只要对应记录的文本值的头32个字节相同，Oracle在收集直方图统计信息时就会认为这些记录在该字段的文本值是相同的，即使实际上它们并不相同。
从DBA_TAB_HISTOGRAMS中看到列B的Frequency类型的直方图统计信息的具体内容，这进一步证实了上述结论：
sql>selecttable_name,endpoint_valuefromdba_tab_histogramswheretable_name='T2'; TABLE_NAMECOLUMN_NAMENDPOINT_NUMBERENDPOINT_VALUE ------------------------------------------------- T2 B 3 5.0563E+35 sql>selectdump('a','16')fromdual; DUMP('A','16') ------------------------------------------------ Typ=96Len=1:61 sql>selectto_number('616161616161616161616161616161','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')fromdual; TO_NUMBER('616161616161616161616161616161','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') ---------------------------------------------------------------------------- 5.0563E+35
通过计算相互符合。
对表T2执行如下sql
select count(*) fromt2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1';
实际返回结果集的Cardinality为1
但从执行计划的结果可以看出CBO错误地评估出上述sql返回结果集的Cardinality为3：
sql>selectcount(*)fromt2whereb='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1'; COUNT(*) ---------- 1 sql>select*fromtable(dbms_xplan.display_cursor(null,'all')); PLAN_TABLE_OUTPUT -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- sql_ID 3n69wfhjuj4sg,childnumber0 ------------------------------------- selectcount(*)fromt2whereb='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1' Planhashvalue:3321871023 --------------------------------------------------------------------------- |Id|Operation |Name|Rows|Bytes|Cost(%cpu)|Time | --------------------------------------------------------------------------- |0|SELECTSTATEMENT| | | | 3(100)| | |1|SORTAGGREGATE| | 1|34| | | |*2|TABLEACCESSFULL|T2| 3|102| 3(0)|00:00:01| ---------------------------------------------------------------------------
这是因为DBA_TAB_HISTOGRAMS中列B的Frequency类型的直方图只有1个Bucket，这会使Oracle认为表T2中只有一个distinct文本值32个'a'，所以对于上述sql而言，Oracle会认为该sql要访问的就是表T2的所有数据。
2.2 Height Balanced类型的直方图
前面介绍到Oracle 12c之前，Frequence类型的直方图对应的Bucket的数量不能超过254，那如果目标列的distinct值的数量大于254呢？此时Oracle会对目标列收集Height Balanced类型的直方图。
zx@ORCL>createtablet1(idnumber); Tablecreated. zx@ORCL>begin 2foriin1..254loop 3forjin1..iloop 4insertintot1values(i); 5endloop; 6endloop; 7commit; 8end; 9/ PL/sqlproceduresuccessfullycompleted. #distinct值的数量为254 zx@ORCL>selectcount(distinct(id))fromt1; COUNT(DISTINCT(ID)) ------------------- 254 #执行一个查询使id列在where条件中 zx@ORCL>select*fromt1whereid=1; ID ---------- 1 #收集直方图信息 zx@ORCL>execdbms_stats.gather_table_stats(ownname=>USER,tabname=>'T1',method_opt=>'forcolumnssizeautoid',estimate_percent=>100); PL/sqlproceduresuccessfullycompleted. zx@ORCL>coltable_namefora10 zx@ORCL>colcolumn_namefora10 zx@ORCL>setlinesize200 zx@ORCL>selecttable_name,histogramfromdba_tab_col_statisticswheretable_name='T1'; TABLE_NAMECOLUMN_NAMNUM_DISTINCTDENSITYNUM_BUCKETSHISTOGRAM -------------------------------------------------------------------------------------------------- T1ID254.000015372251FREQUENCY zx@ORCL>selectendpoint_value,endpoint_numberfromdba_tab_histogramswhereowner=userandtable_name='T1'; ENDPOINT_VALUEENDPOINT_NUMBER ----------------------------- 11 23 36 410 515 .... 25231878 25332131 25432385 254rowsselected.
从输出的结果可以看出ID列上已经有了Frequency类型的直方图。
现在对表T1再插入一条包含不同ID值的记录，然后删除列ID上的直方图信息，再列ID列重新收集直方图信息，然后查询ID列直方图的类型。
zx@ORCL>insertintot1values(255); 1rowcreated. zx@ORCL>commit; Commitcomplete. zx@ORCL>selectcount(distinctid)fromt1; COUNT(DISTINCTID) ----------------- 255 zx@ORCL>execdbms_stats.gather_table_stats(ownname=>USER,method_opt=>'forcolumnssize1id',estimate_percent=>100); PL/sqlproceduresuccessfullycompleted. zx@ORCL>execdbms_stats.gather_table_stats(ownname=>USER,histogramfromdba_tab_col_statisticswheretable_name='T1'; TABLE_NAMECOLUMN_NAMNUM_DISTINCTDENSITYNUM_BUCKETSHISTOGRAM -------------------------------------------------------------------------------------------------- T1ID255.004243247254HEIGHTBALANCED
从输出的结果看现在ID列上的直方图类型已经从之前的Frequency变为了Height Balanced。
对于Height Balanced类型的直方图而言，即当目标列直方图的Bucket的数量小于目标列的distinct值的数量时，Oracle首先会根据目标列对目标表的所有记录按从小到大的顺序排序，然后用目标表总的记录数除以需要使用的Bucket的数量，来决定每个Bucket里需要描述的已经排好序的记录数。假设目标表的总记录数为M，需要使用的Bucket数量为N，每个Bucket里需要描述的已经排好序的记录数为O，则O=M/N；
然后Oracle会用DBA_TAB_HISTOGRAMS、DBA_PART_HISTOGRAMS、DBA_SUBPART_HISTOGRAMS中的每一条记录的ENDPOINT_NUMBER来记录Bucket号，Bucket号从0开始，一直到N。其中0号Bucket里存储的是目标列的最小值，所以0号Bucket所在记录的ENDPOINT_NUMBER值为0，其余Bucket所在记录的ENDPOINT_NUMBER从1一直递增到N，这些记录除了0号Bucket所在记录的ENDPOINT_VALUE值是目标列的最小值外，其他所有记录的ENDPOINT_VALUE值实际上存储的是到此记录所描述述Bucket为此之前所有Bucket描述的记录里目标列的最大值。即除了0号Bucket之外，其他所有记录的ENDPOINT_VALUE值都是用如下公式来计算的：
我们再来使用之前的H表来说明Height Balanced类型的直方图
先删除表H中已存在的Frequency类型的直方图
zx@ORCL>execdbms_stats.gather_table_stats(ownname=>USER,method_opt=>'forcolumnssize1X',estimate_percent=>100); PL/sqlproceduresuccessfullycompleted.
对于Height Balanced类型的直方图而言，目标列直方图的Bucket的数量会小于目标列的distinct值的数量。这里表H有10个distinct值，如果在收集直方图统计信息的时候指定Bucket数量为5，则Oracle就应该收集Height Balanced类型的直方图了。这里收集直方图统计信息时指定method_opt的值为'for columns size 5 X',这里表示在对列X收集直方图时已经指定所用Bucket的数量为5(注意，这里的Bucket数量不含0号Bucket)：
zx@ORCL>execdbms_stats.gather_table_stats(ownname=>USER,method_opt=>'forcolumnssize5X',histogramfromdba_tab_col_statisticswheretable_name='H'; TABLE_NAMECOLUMN_NAMNUM_DISTINCTDENSITYNUM_BUCKETSHISTOGRAM -------------------------------------------------------------------------------------------------- HX10.0852763185HEIGHTBALANCED
从输出来看X列所对应的字段HISTOGRAM的值为HEIGHT BALANCED，这说明X列上已经有Height Balanced类型的直方图。
现在按照刚才介绍的算法算一下DBA_TAB_HISTOGRAM中存储的Height Balanced类型的直方图统计信息的详情。
现在需要使用的Bucket数量为5(不含0号Bucket)表H中总的记录数为40000，所以每个Bucket里所需要描述的记录数为40000/5=8000。
0号Bucket所在记录的ENDPOINT_NUMBER是0，ENDPOINT_VALUE是表H中10个distinct值中最小值1。
使用如下公式计算出每个Bucket所在记录的ENDPOINT_VALUE值：
#Bucket1 zx@ORCL>selectmax(x)from(selectxfromhorderbyx)whererownum<8000; MAX(X) ---------- 7 #Bucket2 zx@ORCL>selectmax(x)from(selectxfromhorderbyx)whererownum<8000*2; MAX(X) ---------- 10 #Bucket3 zx@ORCL>selectmax(x)from(selectxfromhorderbyx)whererownum<8000*3; MAX(X) ---------- 10 #Bucket4 zx@ORCL>selectmax(x)from(selectxfromhorderbyx)whererownum<8000*4; MAX(X) ---------- 32 #Bucket5 zx@ORCL>selectmax(x)from(selectxfromhorderbyx)whererownum<8000*5; MAX(X) ---------- 49
从上述结果可以看到2号Bucket和3号Bucket所对应记录的ENDPOINT_VALUE值都是10，所以Oracle会将2号和3号Bucket合并存储，合并后的记录ENDPOINT_NUMBER值是3，ENDPOINT_VALUE值是10。这里10就是一个popular value。经过上述分析，我们可知DBA_TAB_HISTOGRAMS中的存储的Height Balanced类型的直方图统计信息的详细应为如下所示：
ENDPOINT_NUMBERENDPOINT_VALUE 01 17 310 432 549
我们查询DBA_TAB_HISTOGRAMS中列X的Height Balanced类型的直方图统计信息：
zx@ORCL>selecttable_name,endpoint_valuefromdba_tab_histogramswheretable_name='H'; TABLE_NAMECOLUMN_NAMENDPOINT_NUMBERENDPOINT_VALUE ------------------------------------------------- HX17 HX310 HX432 HX549 HX01
可以看到实际查询结果与我们分析的一致。
3 直方图的收集方法
在Oracle数据库里收集直方图统计信息，通常是在调用DBMS_STATS包中的存储过程GATHER_DATABASE_STATS/GATHER_DICTIONARY_STATS/GATHER_SCHEMA_STAS/GATHER_TABLE_STATS收集统计信息时通过指定输入参数METHOD_OPT来实现。当然也可以使用ANALYZE命令来收集直方图统计信息，比如使用命令“analyze table h compute statistics forcolumns X”来收集表H的列X的直方图统计信息。因为ANALYZE命令在收集统计信息方面有先天的缺陷，所以这里只讨论用DBMS_STATS包来收集直方图统计信息。
DBMS_STATS包中上述存储过程的输入参数METHOD_OPT可以接受如下的输入值：
FOR ALL[INDEXES|HIDDEN] COLUMNS [size_clause]
FOR COLUMNS[size_clause] column|attribute [size_clause] [,column|attribute[size_clause]...]
其中的size_clause必须符合如下的格式：
SIZE {integer|REPEAT|AUTO|SKEWONLY}
size_clause子名中各选项的含义如下所述：
使用SCOTT用户下的表EMP为例来说明：
scott@ORCL>descemp Name Null?Type ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- EMPNO NOTNULLNUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)
1)对表EMP所有有索引的列以自动收集的方式收集直方图统计信息：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'EMP',method_opt=>'for all indexed columns size auto');
2)对表EMP上的列EMPNO和DEPTNO以自动收集的方式收集直方图统计信息：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',method_opt=>'for columns size auto EMPNO DEPTNO');
3)对表EMP上的列EMPNO和DEPTNO收集直方图统计信息，同时指定Bucket数量均为10：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',method_opt=>'for columns size 10 EMPNO DEPTNO');
4)对表EMP上的列EMPNO和DEPTNO收集直方图统计信息，同时指定列EMPNO的Bucket数量为10，列DEPTNO的Bucket数量为5：
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',method_opt=>'for columns EMPNO size 10 DEPTNO size 5');
execdbms_stats.gather_table_stats(ownname=>'SCOTT',method_opt=>'for columns EMPNO size 1');
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',method_opt=>'for all columns size 1');
参考《基于Oracle的sql优化》
官方文档：http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF30103
http://docs.oracle.com/cd/E11882_01/server.112/e41573/stats.htm#PFGRF003
