Oracle 直方图理论

浏览数：90 / 时间：2015年06月12日

一.何为直方图

　　直方图是一种几何形图表，它是根据从生产过程中收集来的质量数据分布情况，画成以组距为底边、以频数为高度的一系列连接起来的直方型矩形图，如图所示

技术分享

二.ORACLE 直方图

　　在Oracle中直方图是一种对数据分布情况进行描述的工具。它会按照某一列不同值出现数量多少，以及出现的频率高低来绘制数据的分布情况，以便能够指导优化器根据数据的分布做出正确的选择。在某些情况下，表的列中的数值分布将会影响优化器使用索引还是执行全表扫描的决策。

　　直方图的使用不受索引的限制，可以在表的任何列上构建直方图。构造直方图最主要的原因就是帮助优化器在表中数据严重偏斜时做出更好的规划。如：一到两个值（status=0和status=1，其中=0有100条数据，=1有1000000条数据，只有这两个值）就构成了表中的大部分数据（数据倾斜），相关查询就可能无法帮助减少满足查询所需的I/O数量（如查询status=1）。创建直方图可以让基于成本的优化器知道何时使用索引才最合适，或何时根据where子句中的值返回表中的80%的记录。

三.直方图分类及原理

等频直方图：针对包含很少不同值的数据集，就是数据分布很均匀。
等高直方图：针对包含很多不同值的数据集，数据分布不均匀。

　　直方图信息的准确性由两个数值决定，一个是bucket的个数，一个是num_distinct的个数。一般来说，bucket的数量越多，关于列数据分布的信息就越准确，但统计直方图花费的时间就越多，oracle中bucket的最大为254个，默认是75个。而sql server中默认是200个。通常情况下当BUCTET < 表的NUM_DISTINCT值得到的是HEIGHT BALANCED（高度平衡）直方图，而当BUCTET > 表的NUM_DISTINCT值的时候得到的是FREQUENCY（频率）直方图。由于满足BUCTET = 表的NUM_DISTINCT值概率较低，所以在Oracle中生成的直方图大部分是HEIGHT BALANCED（高度平衡）直方图。

四.创建直方图

Oracle 通过指定 dbms_stats. gather_table_stats 的 method_opt 参数，来创建直方图的。 method_opt参数说明

method_opt

Accepts either of the following options, or both in combination:

FOR ALL [INDEXED | HIDDEN] COLUMNS [size_clause]
FOR COLUMNS [size clause] column [size_clause] [,column [size_clause]...]

size_clause is defined as size_clause := SIZE {integer | REPEAT | AUTO | SKEWONLY}

column is defined as column := column_name | extension name | extension

- integer : Number of histogram buckets. Must be in the range [1,254].

- REPEAT : Collects histograms only on the columns that already have histograms

- AUTO : Oracle determines the columns on which to collect histograms based on data distribution and the workload of the columns.
- SKEWONLY : Oracle determines the columns on which to collect histograms based on the data distribution of the columns.
- column_name : Name of a column
- extension : can be either a column group in the format of (column_name, Colume_name [, ...]) or an expression

The default is FOR ALL COLUMNS SIZE AUTO.

Examples
An extension can be either a column group (see Example 1) or an expression (see Example 2).

Example 1
DBMS_STATS.GATHER_TABLE_STATS(
    ‘SH‘, ‘SALES‘, method_opt => ‘FOR COLUMNS (empno, deptno)‘); 
Example 2
DBMS_STATS.GATHER_TABLE_STATS(‘SH‘, ‘SALES‘, method_opt => ‘FOR COLUMNS (sal+comm)‘);

Example 3
DBMS_STATS.GATHER_TABLE_STATS(‘SH‘, ‘SALES‘, method_opt => ‘FOR COLUMNS sal size 1‘);
注：1表示1个bucket的数量，即不做直方图处理

Example 4
DBMS_STATS.GATHER_TABLE_STATS(‘SH‘, ‘SALES‘, method_opt => ‘FOR COLUMNS (sal+comm) size 10‘);
注：把sal，comm两列，按(sal+comm)表达式的和分成10个bucket。

Example 5
DBMS_STATS.GATHER_TABLE_STATS(‘SH‘, ‘SALES‘, method_opt => ‘FOR COLUMNS (sal，comm) size 10‘);