Histogram catalogue

This is a list of all articles I’ve written about histograms. Each entry has a date stamp and a short note of the contents. The articles are grouped in a couple of different categories (mainly because this note started life as a list articles on faking the different types of histogram), but otherwise are generally listed most-recent first.

General Articles

• “In-table” predicates (April 2019): how does Oracle handle tabX.col1 = tabX.col2 when there are histograms on the columns.
• Join cardinality with histograms (Oct 2018): Perfect frequency histograms at both ends with no filter predicates.
  • Frequency / Frequency with filter predicates.
  • Frequency / Top Frequency with filter predicates
  • Frequency / Hybrid with filter predicates
  • Frequency / Height-balanced (obsolete) with filter predicates
• Column Stats (Jan 2018): Getting the correct syntax for a complex method_opt.
• Hybrid/Top-N problem (Jan 2018): A bug, fixed in 12.2 with a patch for 12.1.
• Histogram usage (Jan 2018): A histogram reporting my impression of histogram usage
• Histogram myth (Sept 2016): Histograms are just about “rows per value”, a demo of a useful histogram on a primary key column.
• Upgrade threat (Dec 2016): A step you need to take to upgrade from 11.2.0.3 if you have histograms on char() column
• Big number problem (Oct 2015): Older versions of Oracle (pre 12c) can go wrong with data values more than 15 digits long
• Upgrade threat (Oracle-L) (Feb 2014): If you’ve got a big history of histograms then the upgrade from 11.2.0.3 (or earlier) could take a long time
• Overview of histogram enhancements in 12c- pt1 (July 2013)
  • Frequency histograms and Top-N histograms – pt2 (July 2013)
  • Hybrid Histograms in 12c- pt3 (Oct 2013)
• Long strings problem (Oct 2010): Until 12c, Oracle stored at most 32 bytes of a string in the endpoint_actual_value column

Faking histograms

• Hybrid Fake (Oct 2018): How to create a hybrid histogram (using a character column for the example).
• Column Group Histograms (Aug 2018): faking values into a histogram for a column group – only special because we need to derive the value stored.
• Column Group Histograms (Jul 2018): Hacking options to solve a live problem
• Histogram Tip (Sept 2015): An example of creating a simple character-based frequency histogram (published in the IOUG Tips booklet 2014).
• Faking a height-balanced histogram (March 2010): How to create a height-balanced histogram (using a numeric column for the example).
• Faking a frequency histogram (May 2009):  How to create frequency histograms (using a numeric column for the example)
• Top frequency:  I haven’t yet worked out how to fake a Top Frequency histogram. Since it’s little more than a frequency histogram where the optimizer knows there’s a further small percentage (less than one bucketful) of other data, this doesn’t worry me; if necessary I’ll just create a “good enough” frequency histogram and set a suitable density for the remainder.

Articles for redgate/Simpletalk

• Histograms part 1 – Why? (Aug 2013) – why we might need histograms and the types of histogram available to Oracle.
• Histograms part 2 (Oct 2013) – the problems and workarounds for some cases where a height-balanced histogram may be a liability rather than a help.
• Histograms part 3 – When? (Oct 2013) –  examples of using height-balanced histograms to work around design errors.

Other authors

• Top Frequency histograms in 12c (Mohamed Houri, Jan 2016)
• Hybrid histograms in 12c (Mohamed Houri, Jan 2016)