Oracle Scratchpad

September 27, 2018

Column Group Catalogue

Filed under: CBO,Column groups,Indexing,Oracle,Statistics — Jonathan Lewis @ 5:16 pm BST Sep 27,2018

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:


  1. […] Jonathan Lewis writes about Column Group Catalog. […]

    Pingback by Log Buffer #555: A Carnival of the Vanities for DBAs | Official Pythian® Blog — October 19, 2018 @ 7:18 am BST Oct 19,2018 | Reply

  2. […] might help increase the table’s cardinality estimate is to create extended statistics on the column group (puser, […]

    Pingback by Execution Plans | Oracle Scratchpad — July 9, 2020 @ 4:54 pm BST Jul 9,2020 | Reply

  3. […] side effects even for cases where you don’t use this “is null” predicate. (More articles here about column […]

    Pingback by Optimizer Tip | Oracle Scratchpad — December 7, 2021 @ 12:50 pm GMT Dec 7,2021 | Reply

  4. […] Column Group catalogue […]

    Pingback by Catalog Catalogue | Oracle Scratchpad — January 25, 2022 @ 12:22 pm GMT Jan 25,2022 | Reply

  5. […] Column Groups catalogue […]

    Pingback by Statistics catalogue | Oracle Scratchpad — February 21, 2022 @ 10:49 am GMT Feb 21,2022 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: