Oracle Scratchpad

October 4, 2019

opt_estimate catalogue

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

This is just a list of the notes I’ve written about the opt_estimate() hint.

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

I have a couple more drafts on the topic awaiting completion, but if you know of any other articles that would be a good addition to the list feel free to reference them in the comments.

And here’s a start on other articles about opt_estimate() and related hints

  • 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

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 )

Google photo

You are commenting using your Google 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: