Oracle Scratchpad

January 28, 2022

Hinting Catalogue

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

This is a list of all articles I’ve written that talk about hints. Each entry has a date stamp and a short note of the contents. The articles are (may be / will 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.

  • opt_estimate() catalogue: a separate catalogue of articles about the (undocumented) opt_estimate() hint and loosely related hints.
  • ignoring hints catalogue: a separate catalogue of articles about how Oracle doesn’t ignore hints, or examples of bugs, or explanations of the difficulty of hinting correctly.
  • Case Study (June 2022): an example of the SQL Monitor report showing that a no_merge hint was the appropriate solution
  • System_stats() hint (Jan 2022): for Exadata, the optimizer can be told to override the stored system stats.
  • Hash Aggregation 1 (Jan 2022): you may need to hint hash aggregation for some queries that group by then order by the same columns
  • use_nl() redux (Oct 2021): where in its processing does Oracle apply join method hints like use_nl()
  • Ordered hint (Sept 2021): you shouldn’t be using it, and this note shows a surprising “place group by” side effect
  • Distributed Query (Aug 2021): A real tangle when using the rule, driving_site and materialize hints (badly) together
  • Driving_site() in a patch (May 2021): specific example of a general problem – changing an inline hint to an SQL Patch may require a target query block name and a fully qualified table alias.
  • use_nl_with_index (Mar 2021): a description of what it can do, and one (current) omission
  • Index descending First N (Dec 2020): until 19c an first rows in descending order had to be hinted with index_desc()
  • Using the num_index_keys() hint (Nov 2018): an undocumented hint that adjusts the access/filter strategy for index range scans in special cases.
  • Five Hints (Dec 2015): the paper I wrote for a DOAG presentation on the (then) top 5 most most important hints.
  • New Index hints (June 2013): Analysis of how Oracle handles “near misses” on hinting by index description.
  • New Index hints (Nov 2011): Oracle 10g introduced an index hinting by description. Some examples and comments.
  • v$sql_shared_memory (Jan 2009): Even Oracle’s developers get the use_nl() hint wrong.

1 Comment »

  1. […] Hinting catalogue […]

    Pingback by Catalog Catalogue | Oracle Scratchpad — January 28, 2022 @ 3:38 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: