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 be) 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. I also have a separate “Transformations” catalogue available from the main catalogue.
- 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.
- 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()
- Count Rows (Feb 2020): A note about counting rows in a table, with links going back back 20 years.
- Count transformation (Oct 2008): Oracle treats many things as the same as count(*)
- Counting union all (Mar 2012): A case where Oracle doesn’t do the optimum transformation
- Count not transformed (Oct 2012): A crack in the “count(constant)” -> “count(*)” transformation
- Count transformation redux (Jan 2015): Looking at the “unparsed” query for confirmation.
- Counting rowids by accident (Jan 2015): Cursor_Sharing and other exceptions to “count conversion”.
- A counting performance anomaly (Jan 2015): Not count(*) but an internal transformation could do less work and invites a manual transformation.
- “In-table” predicates (April 2019): how does Oracle handle tabX.col1 = tabX.col2 when there are histograms on the columns
- nvl() vs. coalesce() (Feb 2018): a case for using nvl() – because coalesce gives bad cardinality estimates.
- coalesce() vs.nvl() (Jan 2014): a case for using coalesce() – because it “short-circuits” and nvl() doesn’t – but with a warning.
- 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, with a link to a Chinese translation
- 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.
- index_combine() limitation (June 2011): A (not unsurprising) limitation first reported in 11g, still present in 23c.
- Optimizer Environment (May 2007): seeing details of the optimizer parameter settings in v$sql_optimzer_env etc. (Updated to 23c).
External Links
- Index to Simpletalk series on CBO (2015): List of 6 articles in a series on the basics of how the optimizer works, written for Simpletalk, with a link to a Chinese translation
Other Authors
- Index to Stefan Koehler’s articles on the CBO (2015): a list of articles published on the SAP knowledgebase between 2012 and 2015
[…] Optimizer catalogue […]
Pingback by Catalog Catalogue | Oracle Scratchpad — January 28, 2022 @ 2:29 pm GMT Jan 28,2022 |