CTE Catalogue

An index of the articles I’ve written about Subquery Factoring (a.k.a. Common Table Expressions / CTEs, a.k.a. “with” subqueries). Articles are listed in reverse order of publication and each article is date stamped, but the list is not yet complete.

• Notable Change for CTEs (Oct 2023): an optimizer enhancement (and workaround) that may lead to queries crashing in 21c or later versions of19c.
• CTE performance gain (Feb 2022): in-memory cursor duration temp tables (not just for CTEs)
• SQL Table Macros (Aug 2021): a possible alternative to using CTEs (or pipelined functions)
• Distributed Query (Aug 2021): A real tangle when using the rule, driving_site and materialize (key point in the footnote) hints (badly) together
• CTE’s behaving badly with ADG (Jan 2020): an I/O overhead if you use “with subquery” in queries against an active dataguard standby
• An example of a plan changing as an inline view is moved to a factored subquery – even in 19.3 (Sept 2017)
• Case Study: using subquery factoring to reduce the workload for a complex correlated update (Jan 2016)
• Materialization and inconsistent cost calculations (July 2015)
• Using subquery factoring as a strategy for checking for existence (July 2015)
• The exception to the materialization “rule of two” with several extra links (July 2015)
• A brief note on hinting for recursive subquery factoring (Feb 2014)
• Plan variations for materialized CTE involving “not exists” subqueries (May 2012)
• A bug fix for plan hash values with materialized CTEs from 11.2.0.3 onwards (Feb 2012)
• Another plan inconsistency from subquery factoring – addressed in 12.2 (Feb 2012)
• Bug fixes in 11.2.0.3 change some plans that use subquery factoring (Feb 2012)
• Another example where factoring out a subquery changed an execution plan (Sept 2010)
• Rewriting to use subquery factoring can cause changes to execution plans (June 2010)
• When does Oracle materialize a “with” subquery automatically (July 2007)
• Subquery factoring and the merge command (Oct 2006)
•