Database Reference
In-Depth Information
The following is the histogram stored in the data dictionary for the
val3
column:
SQL> SELECT endpoint_value, endpoint_number,
2 endpoint_number - lag(endpoint_number,1,0)
3 OVER (ORDER BY endpoint_number) AS frequency
4 FROM user_tab_histograms
5 WHERE table_name = 'T'
6 AND column_name = 'VAL3'
7 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER FREQUENCY
-------------- --------------- ---------
101 1 1
103 69 68
104 254 185
105 756 502
106 968 212
Compared to the frequency histogram of the
val2
column, there are two differences. First, the bucket for the
value 102 doesn't exist. And that, even though the frequency of value 102 is higher than the frequency of value 101.
In other words, the histogram doesn't represent the top-5 values. Second, the bucket for value 101, even though there
are eight rows with that value, has
endpoint_number
equal to 1. The fact is that a histogram must always contain the
minimum and maximum values. If, as in this case, one of the two values should be discarded because it's not part of
the top-n values, another value is discarded (the one with the lowest frequency), and the frequency of the minimum/
maximum value is set to 1. Note that after such an operation, the rule based on Formula 8-1 must be reevaluated.
The following example shows that, as you might expect, the estimations performed by the query optimizer with
the top frequency histogram are different from the ones performed with the frequency histogram only for the values
without frequency information (101 and 102):
SQL> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val3 = 101;
SQL> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val3 = 102;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val3 = 103;
SQL> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val3 = 104;
SQL> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val3 = 105;
SQL> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val3 = 106;
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
101 32
102 32
103 68
104 185
105 502
106 212
