Database Reference
In-Depth Information
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 characteristics of a frequency histogram are the following:
•
The number of buckets (in other words, the number of categories) is the same as the number
of distinct values. A row in available for each bucket in a view like
user_tab_histograms
.
The
•
endpoint_value
column provides a numerical representation of the value itself.
Hence, for non-numerical datatypes, the actual values must be encoded in a number.
Depending on the data, the datatype, and the version, the actual values might be visible in
the
endpoint_actual_value
column (not shown in the previous output). It's essential to
know that values stored in histograms are distinguished based only on their leading 32 bytes
(64 bytes as of version 12.1). As a result, long fixed prefixes might jeopardize the effectiveness
of histograms. This is especially true for multibyte character sets where each character might
take up to three bytes.
•
The
endpoint_number
column provides the cumulated frequency of the value. To get the
frequency itself, the value of the
endpoint_number
column of the previous row must be
subtracted.
■
in case sampling is used to build a histogram, the frequency information is scaled proportionally to the
sample size. To know the scaling factor, divide the sample size (
sample_size
) by the number of rows (
num_rows
). both
columns are provided by views like
user_tab_statistics
.
Caution
The following example shows how the query optimizer takes advantage of the frequency histogram to estimate
precisely the number of rows returned by a query (the
cardinality
) that has a predicate on the
val2
column (detailed
information about the
EXPLAIN PLAN
statement is provided in Chapter 10):
SQL> EXPLAIN PLAN SET STATEMENT_ID '101' FOR SELECT * FROM t WHERE val2 = 101;
SQL> EXPLAIN PLAN SET STATEMENT_ID '102' FOR SELECT * FROM t WHERE val2 = 102;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103' FOR SELECT * FROM t WHERE val2 = 103;
SQL> EXPLAIN PLAN SET STATEMENT_ID '104' FOR SELECT * FROM t WHERE val2 = 104;
SQL> EXPLAIN PLAN SET STATEMENT_ID '105' FOR SELECT * FROM t WHERE val2 = 105;
SQL> EXPLAIN PLAN SET STATEMENT_ID '106' FOR SELECT * FROM t WHERE val2 = 106;
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0;
