[PT]Column Histogram Statistics

(The descirption is from Oracle Document: Performance Tuning)

Column statistics may be stored as histograms. These histograms provide accurate estimates of the distribution of column data. Histograms provide improved selectivity estimates in the presence of data skew, resulting in optimal execution plans with nonuniform data distributions.
Oracle uses two types of histograms for column statistics: height-balanced histograms and frequency histograms. The type of histogram is stored in the HISTOGRAM column of the *TAB_COL_STATISTICS views (USER and DBA). This column can have values of HEIGHT BALANCED, FREQUENCY, or NONE.

Height-Balanced HistogramsIn a height-balanced 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.

Frequency HistogramsIn a frequency 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.

A test about histogram

SQL> create table hist_test(cola NUMBER, colb VARCHAR2(10));

表已建立。

SQL> begin
2 <>
3 for i in 1..20 loop
4 <>
5 for j in 1..i loop
6 insert into hist_test values(i,'TEST');
7 end loop inner;
8 end loop outer;
9 end;
10 /

PL/SQL 過程已成功完成。

SQL> select count(*) from hist_test;

COUNT(*)
----------
210

SQL> select dbms_stats.get_param('METHOD_OPT') from dual;

DBMS_STATS.GET_PARAM('METHOD_OPT')
------------------------------------
FOR ALL COLUMNS SIZE AUTO

SQL> exec dbms_stats.gather_table_stats(ownname=>'YCHEN',tabname=>'HIST_TEST');

PL/SQL 過程已成功完成。

SQL> SELECT column_name, num_distinct, num_buckets, histogram
2 FROM USER_TAB_COL_STATISTICS
3 WHERE table_name = 'HIST_TEST';

COLUM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
----- ------------ ----------- ---------------
COLA 20 1 NONE
COLB 1 1 NONE

SQL> SELECT column_name, endpoint_number, endpoint_value
2 FROM USER_TAB_HISTOGRAMS
3 WHERE table_name= 'HIST_TEST'
4 ORDER BY column_name, endpoint_number;

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE
----- --------------- --------------
COLA 0 1
COLA 1 20
COLB 0 4.3756E+35
COLB 1 4.3756E+35

SQL> exec dbms_stats.gather_table_stats(ownname=>'YCHEN',tabname=>'HIST_TEST',me
thod_opt=>'FOR COLUMNS SIZE 10 COLA');

PL/SQL 過程已成功完成。

SQL> SELECT column_name, num_distinct, num_buckets, histogram
2 FROM USER_TAB_COL_STATISTICS
3 WHERE table_name = 'HIST_TEST' and column_name = 'COLA';

COLUM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
----- ------------ ----------- ---------------
COLA 20 10 HEIGHT BALANCED

SQL> SELECT column_name, endpoint_number, endpoint_value
2 FROM USER_TAB_HISTOGRAMS
3 WHERE table_name= 'HIST_TEST' and column_name = 'COLA'
4 ORDER BY column_name, endpoint_number;

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE
----- --------------- --------------
COLA 0 1
COLA 1 7
COLA 2 9
COLA 3 11
COLA 4 13
COLA 5 15
COLA 6 16
COLA 7 17
COLA 8 18
COLA 9 19
COLA 10 20

已選擇11行。

SQL> exec dbms_stats.gather_table_stats(ownname=>'YCHEN',tabname=>'HIST_TEST',me
thod_opt=>'FOR COLUMNS SIZE 30 COLA');

PL/SQL 過程已成功完成。

SQL> SELECT column_name, num_distinct, num_buckets, histogram
2 FROM USER_TAB_COL_STATISTICS
3 WHERE table_name = 'HIST_TEST' and column_name = 'COLA';

COLUM NUM_DISTINCT NUM_BUCKETS HISTOGRAM
----- ------------ ----------- ---------------
COLA 20 20 FREQUENCY

SQL> SELECT column_name, endpoint_number, endpoint_value
2 FROM USER_TAB_HISTOGRAMS
3 WHERE table_name= 'HIST_TEST' and column_name = 'COLA'
4 ORDER BY column_name, endpoint_number;

COLUM ENDPOINT_NUMBER ENDPOINT_VALUE
----- --------------- --------------
COLA 1 1
COLA 3 2
COLA 6 3
COLA 10 4
COLA 15 5
COLA 21 6
COLA 28 7
COLA 36 8
COLA 45 9
COLA 55 10
COLA 66 11
COLA 78 12
COLA 91 13
COLA 105 14
COLA 120 15
COLA 136 16
COLA 153 17
COLA 171 18
COLA 190 19
COLA 210 20

已選擇20行。

[@more@]