Collect histogram information from Oracle

The histogram is useful when the column data distribution is uneven. The query optimizer needs the histogram information to make a correct estimation. There are two kinds of histogram with frequency and height. This article still uses the previous test table. The article links to collect table and column statistics in Oracle.

I. Frequency Histogram

The frequency histogram uses the cumulative frequency instead of the frequency. The following endpoint_number is the cumulative number of values.

SELECT ENDPOINT_VALUE,
ENDPOINT_NUMBER,
ENDPOINT_NUMBER-LAG (ENDPOINT_NUMBER, 1, 0) OVER (order by ENDPOINT_NUMBER) AS FREQUENCY
FROM USER_TAB_HISTOGRAMS
WHERE TABLE_NAME = 'T'
AND COLUMN_NAME = 'val2'
Order by ENDPOINT_NUMBER;

ENDPOINT_VALUE	ENDPOINT_NUMBER	FREQUENCY
101	8	8
102	33	25
103	101	68
104	286	185
105	788	502
106	1000	212

The essential features of the frequency histogram are:

① The number of buckets (number of categories) is equal to the total number of unique values.

② This is provided by the column endpoint_value.

③ The column endpoint_number is the cumulative number of occurrences of the value. Only the current endpoint_number minus the previous endpoint_number is the number of occurrences of the current value.

The following shows how the query optimizer uses the frequency histogram to accurately estimate the cardinality returned by the query based on column val2 filtering ).

Explain plan set STATEMENT_ID '2013' for select * FROM t WHERE val2 = 101;
Explain plan set STATEMENT_ID '2013' for select * FROM t WHERE val2 = 102;
Explain plan set STATEMENT_ID '2013' for select * FROM t WHERE val2 = 103;
Explain plan set STATEMENT_ID '2013' for select * FROM t WHERE val2 = 104;
Explain plan set STATEMENT_ID '2013' for select * FROM t WHERE val2 = 105;
Explain plan set STATEMENT_ID '2013' for select * FROM t WHERE val2 = 106;

SELECT STATEMENT_ID, cardinality from plan_table where id = 0;

STATEMENT_ID	CARDINALITY
101	8
102	25
103	68
104	185
105	502
106	212

When the number of unique values in a column is greater than the maximum number allowed by the bucket (254), the frequency histogram cannot be used. In this case, the High-Level Histogram should be used.

For more details, please continue to read the highlights on the next page:

32-byte limit-Oracle histogram Optimization

[Oracle tutorial] Use PL/SQL to draw a Histogram

1 2 Next Page