In Oracle, there are 4 histogram types:
frequency
height-balanced
top-n frequency
hybrid
Frequency histogram: for low numbers of distinct values. If ndv < bucket count limit, then each ndv gets a histogram bucket. If ndv > limit, frequency histogram is disqualified.
Height-balanced histogram: histogram buckets with approximately the same number of rows in each bucket
Top-n Frequency: lowest value, highest value, n-2 most popular values in between as bucket end-points.
Hybrid: Top-n Frequency not qualified because N end-point values do not cross threshold to qualify.
In SQL Server 2016 (as has been the case for several major versions), one histogram type is available. Of the histogram types available in Oracle 12cR2, it is most similar to the hybrid histogram. For a nullable column, a NULL histogram bucket will be available. For non-null values, up to 200 additional buckets are available. The desired number of buckets cannot be specified. For values of particular significance, or in order to accommodate skew in data, filtered statistics can be created. For filtered statistics to reliably be consulted by the optimizer, a query filter predicate must match the predicate of the filtered statistics.
Resources
March 2017 Whitepaper (1.1 mb pdf)
Understanding Optimizer Statistics with Oracle Database 12c Release 2
Three part Oracle Database Histogram series from Jonathan Lewis
Another three part Oracle Database Histogram series from Jonathan Lewis
Two part Oracle Database Histogram series from Mohamed Houri
No comments:
Post a Comment