Database Reference
In-Depth Information
52 735 80 80
53 799 64 64
54 875 76 76
56 955 80 30
59 994 39 6
63 1000 6 1
In the preceding output, notice that on the one hand, the endpoint_number column provides information about
the number of rows associated to a bucket, and on the other hand, the endpoint_repeat_count column provides the
frequency of the endpoint value. Based on this information, the estimations performed by the query optimizer for the
endpoint values can be accurate. Here's an example:
SQL> EXPLAIN PLAN SET STATEMENT_ID '44' FOR SELECT * FROM t WHERE val1 = 44;
SQL> EXPLAIN PLAN SET STATEMENT_ID '50' FOR SELECT * FROM t WHERE val1 = 50;
SQL> EXPLAIN PLAN SET STATEMENT_ID '56' FOR SELECT * FROM t WHERE val1 = 56;
SQL> SELECT statement_id, cardinality
2 FROM plan_table
3 WHERE id = 0
4 ORDER BY statement_id;
STATEMENT_ID CARDINALITY
------------ -----------
44 26
50 102
56 30
The information provided by an hybrid histogram is much better than that provided by a height-balanced histogram.
For this reason, from version 12.1 onward, height-balanced histograms can and should be completely avoided.
Tip
No Histogram
It's useful to note that the user_tab_histograms view shows two rows for each column without a histogram. This is
because the minimum and maximum values are stored in endpoint numbers 0 and 1, respectively. For example, the
content for the id column, which has no histogram, is the following:
SQL> SELECT endpoint_value, endpoint_number
2 FROM user_tab_histograms
3 WHERE table_name = 'T'
4 AND column_name = 'ID';
ENDPOINT_VALUE ENDPOINT_NUMBER
-------------- ---------------
1 0
1000 1
 
 
Search WWH ::




Custom Search