Database Reference
In-Depth Information
STATEMENT_ID CARDINALITY
------------ -----------
101 8
102 25
103 68
104 185
105 502
106 212
In the preceding example, all predicates reference only values that are represented in the histogram. But what
happens when other values are used? Up to and including 10.2.0.3, the query optimizer used 1 as frequency. From
version 10.2.0.4 onward, there are two distinct situations to consider. First, if the value is between the minimum and
the maximum values, the query optimizer takes the lowest frequency of all values represented in the histogram and
divides it by two. Second, if the value is outside the range covered by the histogram, the frequency depends on the
distance from the lowest/maximum value. The following example illustrates:
SQL> EXPLAIN PLAN SET STATEMENT_ID '096' FOR SELECT * FROM t WHERE val2 = 96;
SQL> EXPLAIN PLAN SET STATEMENT_ID '098' FOR SELECT * FROM t WHERE val2 = 98;
SQL> EXPLAIN PLAN SET STATEMENT_ID '100' FOR SELECT * FROM t WHERE val2 = 100;
SQL> EXPLAIN PLAN SET STATEMENT_ID '103.5' FOR SELECT * FROM t WHERE val2 = 103.5;
SQL> EXPLAIN PLAN SET STATEMENT_ID '107' FOR SELECT * FROM t WHERE val2 = 107;
SQL> EXPLAIN PLAN SET STATEMENT_ID '109' FOR SELECT * FROM t WHERE val2 = 109;
SQL> EXPLAIN PLAN SET STATEMENT_ID '111' FOR SELECT * FROM t WHERE val2 = 111;
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
096 1
098 2
100 3
103.5 4
107 3
109 2
111 1
Height-Balanced Histograms
When the number of distinct values is greater than the maximum number of allowed buckets (with the
dbms_stats
package, there is both a hard limit and the possibility to specify an even lower value), you can't use frequency
histograms because they support a single value per bucket. This is where height-balanced histograms become useful.
To create a height-balanced histogram, think of the following procedure. First, a frequency histogram is created.
Then, as shown in Figure
8-3
, the values of the frequency histogram are stacked in a pile. Finally, the pile is divided
into several buckets of exactly the same height. For example, in Figure
8-3
the pile is split into five buckets.
