Oracle Scratchpad

January 28, 2022

Optimizer catalogue

Filed under: Oracle — Jonathan Lewis @ 2:28 pm GMT Jan 28,2022

This is a list of all articles I’ve written that talk about the mechanisms the optimizer can choose. Each entry has a date stamp and a short note of the contents. The articles are (may eventually) grouped in a couple of different categories, but otherwise are generally listed most-recent first.

I am currently working backwards through my entire blog adding articles to catalogues as I go, so any individual catalogue may grow very slowly.

  • Join view delete (May 2022): ANSI expansion problems with deletes from join views
  • Adaptive Plans (April 2022): what they can cost and how block then selectively
  • Generated predicates (March 2022): virtual columns from “function-based” indexes may allow the optimizer to generate predicates that you weren’t expecting to see
  • Hash Aggregation 1 (Jan 2022): you may need to hint hash aggregation for some queries that group by then order by the same columns
  • Hash Aggregation 2 (Jan 2022): Workarea operations listed in execution plans may have difference names in the v$sql_workarea.
  • use_nl() redux (Oct 2021): where in its processing does Oracle apply join method hints like use_nl()
  • A little tweak in 19c (July 2021): the optimizer adding an “is not nulll” predicate that changes the cost of an execution path.
  • Join Elimination bug (Mar 2021): inconsistent behaviour when combined with deferrable constraints.
  • use_nl_with_index (Mar 2021): a description of what it can do, and one (current) omission
  • Adaptive Plan bug (Feb 2021): An adaptive plan (on partitioned tables with missing statistics) producing wrong results even in 21.3
  • Optimizer arithmetic (Jan 2021): A little case study of the optimizer’s cardinality calculations.
  • Between (Jan 2021): a small optimisation detail that appears for “between” clauses.
  • Index descending First N (Dec 2020): until 19c an first rows in descending order had to be hinted with index_desc()
  • Character Selectivity (Apr 2017): How does the optimizer calculate the cardinality of column like ‘xxx%’
  • Index to Simpletalk series on CBO (2015): link to an introductory series of 6 articles on the basics of how the optimizer works that I wrote for Simpletalk.
  • Quantum Data (Nov 2014): an inconsistency in cardinality estimates for “out of range” data – still the same in the latest versions
  • Index FFS Deletes (June 2014): From 12.2 Oracle can use an index fast full scan to drive a delete (or, in limited cases) an update. Still not able to drive a select.

Other Authors

1 Comment »

  1. […] Optimizer catalogue […]

    Pingback by Catalog Catalogue | Oracle Scratchpad — January 28, 2022 @ 2:29 pm GMT Jan 28,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: