Hinting Catalogue

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.