Oracle Scratchpad

October 4, 2019

opt_estimate catalogue

Filed under: CBO,Hints,Oracle — Jonathan Lewis @ 10:10 am BST Oct 4,2019

This catalogue started life as a list of the notes I’ve written about the opt_estimate() hint, but has been extended to include links to related hints (cardinality, system_stats, table_stats etc.) so there is some grouping of articles. Otherwise the article are generally listed in reverse order of publication.


  • System_stats() hint (Jan 2022) – for Exadata, the optimizer can be told to override the stored system stats.


  • opt_estimate (May 2017) – using the hint to affect index calculations: index_scan and index_filter
  • opt_estimate 2 (June 2019) – applying the hint to nested loop joins, options: nlj_index_scan and nlj_index_filter
  • opt_estimate 3 (June 2019) – a couple of little-known options for the hint, “group_by” and “having”.
  • opt_estimate 4 (July 2019) – applying the hint at the query block level: particularly useful for CTEs (“with subquery”) and non-mergeable views.
  • opt_estimate 5 (July 2019) – a story of failure: trying to use opt_estimate to push predicates into a union all view.

Other authors

  • table_stats() – A note from Roger MacNicol on the correct syntax and use of the table_stats() hint (currently hosted with permission at


  1. Unfortunately, Roger’s article about the table_stats() hint is not available anymore :(

    Comment by Sayan Malakshinov — November 30, 2021 @ 4:18 pm GMT Nov 30,2021 | Reply

    • Sayan,

      Thanks for letting me know.
      I’ve added a detai to the blog note that I think came from the original summarising the syntax and giving a simple example.

      I’ve also got a note that you should not use the @qb_name syntax, but that might have been a detail that I had discovered rather than a comment copied from Roger – so the statement should be treated with a little caution.

      Jonathan Lewis

      Comment by Jonathan Lewis — December 1, 2021 @ 4:58 pm GMT Dec 1,2021 | Reply

  2. Thanks Jonathan!
    Btw, Roger’s blog was deleted, because of inactivity for longer than a year, but he gave me all his articles (he backed them up before deleting) and allowed to republish them, so I’ve restored all of them, and here is the new link to the post about correct syntax of the table_stats hint:

    Best regards,

    Comment by Sayan Malakshinov — December 2, 2021 @ 12:20 pm GMT Dec 2,2021 | Reply

    • Sayan,

      Thanks for that.

      Somewhere on my blog I think I’ve got references to Roger’s articles on “small table threshold” etc. that I will also need to update to point to your blog.

      Jonathan Lewis

      Comment by Jonathan Lewis — December 3, 2021 @ 11:36 am GMT Dec 3,2021 | Reply

  3. […] opt_estimate() hint catalogue […]

    Pingback by Catalog Catalogue | Oracle Scratchpad — January 25, 2022 @ 12:22 pm GMT Jan 25,2022 | Reply

  4. […] opt_estimate() catalogue (Oct 2019): a catalogue of articles about the (undocumented) opt_estimate() hint and related hints. […]

    Pingback by Hinting Catalogue | Oracle Scratchpad — February 19, 2022 @ 10:44 am GMT Feb 19,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: