Transformations Catalogue

This is a list of all articles I’ve written about optimizer transformations. Many of these articles will have been listed in the Execution Plans catalogue and Optimizer Catalogue. Each entry has a date stamp and a short note of the contents. The articles are generally listed most-recent first, but there is a separate index to a series I have recently started writing for Simpletalk which lists the articles in the order they were written. I’ve also started adding articles from other authors – just one so far from Nigel Bayliss

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

• Push Group By (Nov 2023): New to 23c, pushing a “group by” clauses inside a “union all” view.
• Removing Subqueries (Oct 2023): notes on how the optimizer can use analytic (window) functions to remove some subqueries from your SQL – and why it may not be happening right now.
• Losing OR-expansion (Jan 2023): an upgrade may block a concatenation plan but not replace it with Or-expansion.
• Upgrade issues (July 2022): an example of how new transformations can change plans that had been hinted as completely as possible.
• Join view delete (May 2022): ANSI expansion problems with deletes from join views
• Ordered hint (Sept 2021) – An accidental example of two appearances of the “place group by” transformation in a single plan
• Join Elimination bug (Mar 2021): inconsistent behaviour when combined with deferrable constraints.
• Emulating Star transformations (April 2011): Starts with a description of the real thing.
  • Alternative emulation strategies (April 2011): Variations using IN subqueries and Intersect.
• Treating indexes as tables (May 2010): non-trivial query rewrites that can do wonders for performance.
• Group by placement (Dec 2008): “aggregating early” and the place_group_by() hint – introduction
• Filter Subqueries (Nov 2006): An early (but still relevant) explanation of the random performance impact of Scalar Subquery Caching

A list of articles written for Simpletalk (in order of publication)

• The basics of handling a simple existence subquery (Jan 2022)
• NOT IN subqueries and deleting with NULL (Feb 2022)
• Scalar subquery caching and subquery pushing (Mar 2022)
• Coalescing and removing subqueries (April 2022)
• OR-Expansion – the “new” concatenation (Oct 2022)

Other Authors:

• OR-expansion (Nigel Bayliss Jan 2020): an example of how new transformations can change plans that had been hinted as completely as possible.