To improve optimizer estimates in case of skewed data distribution , histograms can be created. Prior to 12c, based on No. of distinct values (NDV) in a column two types of histograms could be created :
if no. of buckets >= NDV, frequency histogram is created and the optimizer makes accurate estimates.
If no. of buckets < NDV, height balanced histogram is created and accuracy of optimizer estimates depends on whether a key value is an endpoint or not.
The problem of optimizer mis-estimates in case of height balanced histograms is resolved to a large extent in Oracle Database 12c by introducing top-frequency and hybrid histograms which are created if no. of buckets < NDV.
I will present a webinar on “Histograms: Pre-12c and now” on Saturday, November 7th at 10:00 AM – 11:00 AM (IST) organized by All India Oracle User Group – North India Chapter.
This webinar explores Pre as well post-12c histograms while highlighting the top-frequency and hybrid histograms introduced in Oracle Database 12c.
Hope to meet you at the webinar!!!