昨天一同事问到关于histogram的问题，虽然知道是和统计有关的，但详细的

并不是很清楚，于是查了些资料......

1.       概述

Oracle使用两种柱状统计：height_based和value_based(也称为高度柱状图和频率柱状图)，柱状图的类型存储在视图[user dba all]_tab_col_statistics之类的系统表中，值包含：HEIGHT BALANCED, FREQUENCY, or NONE。

In a height-balanced(height_based) histogram, the column values are divided into bands so that each band contains approximately the same number of rows. The useful information that the histogram provides is where in the range of values the endpoints fall.

In a frequency(value_based) histogram, each value of the column corresponds to a single bucket of the histogram. Each bucket contains the number of occurrences of that single value. Frequency histograms are automatically created instead of height-balanced histograms when the number of distinct values is less than or equal to the number of histogram buckets specified. Frequency histograms can be viewed using the [user dba all]_TAB_HISTOGRAMS views.

2.     相关数据字典[user dba all]_histograms, [user dba all]_part_histograms(分区表), [user dba all]_sub_part_histograms, [user dba all]_tab_histograms

3.     柱状图产生的方法

l   dbms_stats.gather_table_stats(,,METHOD_OPT => FOR COLUMNS SIZE );

若METHOD_OPT 设置为FOR ALL COLUMNS SIZE AUTO，Oracle将自动决定哪些列需要柱状图以及每个柱状图的bucket size，默认为75 buckets，最大值是254

l   ANALYZE TABLE HIST_EXAMPLE COMPUTE STATISTICS FOR ALL [INDEXED]  COLUMNS [SIZE] ;