Database Reference
In-Depth Information
VAL1 COUNT(*) PERCENT
---------- ---------- -------
39 2 0.2
41 4 0.4
42 13 1.3
43 21 2.1
44 26 2.6
45 54 5.4
46 66 6.6
47 86 8.6
48 81 8.1
49 97 9.7
50 102 10.2
51 103 10.3
52 80 8.0
53 64 6.4
54 76 7.6
55 50 5.0
56 30 3.0
57 21 2.1
58 12 1.2
59 6 0.6
60 5 0.5
63 1 0.1
If the database engine is instructed to create a histogram with ten buckets, neither a frequency histogram nor
a top frequency histogram can be used. The former isn't applicable because the number of buckets is greater than
the number of distict values. The latter isn't applicable because the top-10 values represent only about 80% of the
rows (according to Formula 8-1, 90% is required; 90 = 100 - 100/10). As a result, a hybrid histogram that provides the
following information is built:
SQL> SELECT endpoint_value, endpoint_number,
2 endpoint_number - lag(endpoint_number,1,0)
3 OVER (ORDER BY endpoint_number) AS count,
4 endpoint_repeat_count
5 FROM user_tab_histograms
6 WHERE table_name = 'T'
7 AND column_name = 'VAL1'
8 ORDER BY endpoint_number;
ENDPOINT_VALUE ENDPOINT_NUMBER COUNT ENDPOINT_REPEAT_COUNT
-------------- --------------- ---------- ---------------------
39 2 2 2
44 66 64 26
45 120 54 54
46 186 66 66
47 272 86 86
48 353 81 81
49 450 97 97
50 552 102 102
