Column Group Catalogue

I seem to have written a number of aricles about column groups – the rather special, and most useful, variant on extended stats. To make it as easy as possible to find the right article I’ve decided to produce a little catalogue (catalog) of all the relevant articles, with a little note about the topic each article covers. Some of the articles will link to others in the list, and there are a few items in the list from other blogs. There are also a few items which are the titles of drafts which have been hanging around for the last few years.

• A gap in the algorithm (Dec 2021): the optimizer doesn’t use the column group where it should for OR’ed predicates
• Optimizer Tip (Sept 2021): reprint of a note for IOUG 2015 on column groups and table_cached_blocks
• NDV oddity for groups (Oct 2020): A column group will have at least as many values as each of its components – but gathered stats don’t always report that.
• A threat from nulls in indexes (Dec 2018): Sometimes you have to stop the optimizer using index.distinct_keys for cardinality estimates
• An optimizer inconsistency (Oct 2018): Column groups lose information about the frequency of nulls in the underlying data.
• Column group histograms (Aug 2018): Formal coding method
• Column group histograms (Jul 2018): Hacking to solve a problem
• Column groups (Mar 2018): A “cosmetic” change to a query makes increased use of column groups possible.
• Index out of range (Mar 2017): Effects of “out of range” predicates on indexes and column groups
• Extended Stats (Dec 2016): A dirty workaround to the limited number of column groups allowed.
• Adaptive Mayhem (Aug 2016): The anguish of 12.1 and adaptive statistics.
• Index Sanity (June 2016): A very old demonstration – don’t drop an index just because you’re not using it.
• Column groups (Apr 2016): A bug
• Automatic column groups (Dec 2015): A threat due to upgrading to 12.1
• Upgrades (Dec 2015): A summary of a round-table CBO session that covered column groups and virtual columns
• Column groups (Dec 2015): A problem with column groups and char() types.
• Column groups (Nov 2015): A predicate “column is null” will disable the use of any related column groups.
• Extendend Stats (May 2014): Under the covers with column groups.
• Extended Stats (Sep 2013): How a typing error could introduce extra column groups
• Extended Stats (April 2012): The stats on a column group will not be used if a predicate on an underlying column queries values outside the low/high range
• Index Upgrades (Mar 2012): the optimizer can use distinct_keys from indexes in the same way it uses column group stats
•  
• Correlation Strength (draft):
• Column groups – a bug (draft):
• Case study: Dropping indexes (draft):
• col_usage$ (draft)
•  

Other Authors:

• Upgrade problem using datapump (Bobby Durrett, Dec 2019) – importing from 11g to 19c using datapump producing ORA-39083
• Excessive Parsing (Nenad Noveljic, Jan 2016):
• Column Group bypassed (Chris Antognini, Feb 2014): The “histogram” problem of column groups, reporting a known bug number and fix_control to address the issue.
• ORA-20008: Number of extensions … (Iordan Iotzov, Oct 2012): Getting the best from a limited number of column groups.
•  Extended Stats in 11g (Greg Rahn, Oct 2007): An early example of using extended stats to create a column group.