Oracle-直方图介绍

直方图概述

  好久没来墨天轮了，因为最近一个项目每日每夜的赶工。好在项目终于步入正轨了，也有闲情逸致总结一下知识点。结合今天的每日一题，重新稳固了一下oracle几种直方图。
  直方图用白话而言就是为了更详细的描述某个列的所有值出现的频率，这样个有助于在使用此列进行查询的时候选择更优的执行计划,如下表table_temp中共有10000万行，a列有8个不同的值。

select * from table_temp where a='6';
--a=6的数据共用9500条，如果没有直方图，oracle会任务a列是均匀分布的，总行数（10000）/(最大值6-最小值1)=2000，既每一个a值都会有2000条记录，会选择走索引。但是对于a=6这一行而言，如果走索引还会涉及到根据rowid回表的情况，所以逻辑读比全表扫描还会大。因此针对于a=6，使用全表扫描才是最优的访问路径。

  根据上面的例子，直方图的出现就是为了解决上述问题的，oracle在12c之前有2中直方图频率直方图（Frequence）和高平衡直方图（Height-Balanced），Oracle在12c之后，引进高频（top frequency）和混合（hybrid）直方图，来替代高平衡直方图统计不准的问题。oracle是根据下面的几个要素推导出该收集什么样的直方图。

关键要素：
NDV：某列中不同值的数量（distinct）.如一个列只包含a、b、c、d，则该列的NDV为4；
n：直方图bucket数量，默认值也是254；
p：百分比阈值，p=(1-(1/n))*100，如n=254，则p=99.6；
endpoint value：端点值，每个bucket中最大的值
popular（受欢迎）：端点值（endpoint value）出现次数大于1
nonpopular（不受欢迎）：端点值（endpoint value）出现次数等于1
endpoint number：唯一标识bucket的数字，每类直方图有不同的编号规则，频率直方图是重复数的累积，高平衡直方图则是顺序。

--查询直方图信息的语句
sys@orcl> select table_name, column_name, num_distinct, histogram，NUM_BUCKETS
  2    from dba_tab_col_statistics
  3    where table_name = 'TABLE_TEMP'
  4    and column_name = 'A';

TABLE_NAME           COLUMN_NAME          NUM_DISTINCT HISTOGRAM   NUM_BUCKETS
-------------------- -------------------- ------------ ------------------------
COUNTRIES            A                       6     FREQUENCY          6

--HISTOGRAM列表示直方图类型
--NUM_DISTINCT 表示NDV，列中不同值得数量
--NUM_BUCKETS  表示直方图buckets的数量


--查询endpoint number以及endpoint value
sys@orcl> select endpoint_number, endpoint_value
  2    from dba_histograms
  3   where table_name = 'TABLE_TEMP'
  4     and column_name = 'A';
endpoint_number   endpoint_value
---------------- ----------------
    1                0
    100              1
    201              2
    301              3
    401              4
    501              5
    10000            6
    10001            7    

以下模拟生成各种直方图的场景

   以下例子只是模拟生成不同直方图的条件，单纯举例，数字不是比较准确
频率直方图

A、	NDV需小于等于n，n值是直方图的buckets数值(默认为254)；
B、	收集统计信息时estimate_percent参数需设置为用户指定的值或auto_sample_size

 begin
      dbms_stats.gather_table_stats(ownname    => 'SCOTT',
                                    tabname    => 'TABLE_TEMP',
                                    method_opt => 'FOR COLUMNS A');
    end;
    /

   查询 endpoint_number, endpoint_value的信息，可以看到频率直方图中的endpoint_number=上一个endpoint_number+当前bucket中值对应行得数量，如果当前endpoint_number-上一个endpoint_number>=2则表示是popular，否则是nopopular，此例中a=0和a=7是nopopular。

sys@orcl> select endpoint_number, endpoint_value
  2    from dba_histograms
  3   where table_name = 'TABLE_TEMP'
  4     and column_name = 'A';
endpoint_number   endpoint_value
---------------- ----------------
    1                0
    100              1
    201              2
    301              3
    401              4
    501              5
    10000            6
    10001            7    

高平衡直方图

A、	NDV大于n值，n为直方图buckets数量(默认值为254)，此例中n为3;
B、	收集统计信息时estimate_percent不能设置为auto_sample_size.
--FOR COLUMNS COUNTRY_SUBREGION_ID SIZE 7指定了bucket的数量是7,7<NDV
begin
      dbms_stats.gather_table_stats(ownname          => 'SCOTT',
                                   tabname          => 'TABLE_TEMP',
                                   method_opt       => 'FOR COLUMNS A SIZE 3',
                                    estimate_percent => 100);
    end;
    /

Top Frequency直方图

A、	NDV大于n值，n为直方图buckets数量(默认值为254)，此例中n为3;
B、	前n个频繁值所占行的百分比大于等于阈值p，p=(1-(1/n))*100,此例中n=3，前n频繁值所占行的
百分比=（9500+100+100）/10000=0.97,阈值=（1-（1/3））*100=0.66;
C、	收集统计信息时estimate_percent参数需设置为auto_sample_size.
 begin
      dbms_stats.gather_table_stats(ownname    => 'SCOTT',
                                    tabname    => 'TABLE_TEMP',
                                    method_opt => 'FOR COLUMNS A SIZE 3');
   end;
   /

Top Frequency直方图
   还是按照表TABLE_TEMP可能不太好举例，只能重新做一遍数据

A、	NDV大于n，n为直方图bucket数(默认为254)，此例中n为1;
B、	前n个频繁值所占行的百分比小于阈值p，p=(1-(1/n))*100，此例中n=3，前n频繁值所占行的
百分比=（500+500+300）/2500=0.52,阈值p=（1-（1/3））*100=0.76
C、	DBMS_STATS统计信息收集时estimate_percent参数设置为AUTO_SAMPLE_SIZE.
 begin
      dbms_stats.gather_table_stats(ownname    => 'SCOTT',
                                    tabname    => 'TABLE_TEMP',
                                    method_opt => 'FOR COLUMNS A SIZE 3');
    end;
    /

优化器如何选择访问路径

   直方图的生成规则描述了一下，那么优化器是怎么根据直方图决定是否走索引的那？popular和nopopular的计算公式不一样。

popular基数的估算公式：
cardinality of popular value = 
  (num of rows in table) * 
  (num of endpoints spanned by this value / total num of endpoints)

根据上述频率直方图的例子举例
popular（a=5）的估算值=10000*（100/10000）

nopopular基数的估算公式：
cardinality of nonpopular value =
  (num of rows in table) * density

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