Oracle’s OPT_ESTIMATE hint: Usage Guide

Posted in: Technical Track

It’s one of those hints that you’ve heard about many times, but all the information is scattered, and finding what you need is really hard.

Many (including myself) have expressed the deepest desire to see it documented, but that never happened. I’ve just troubleshot a problem and used this hint a lot during my “what if” scenario testing, and found this hint to be quite useful. Thus, I decided to document it here on the blog.

I will come back and update this blog as I discover new parameters, and feel free to suggest what you’ve found in the comments. If you can, include an example to illustrate usage.

Note that this usage guide is not official, and is built upon by my best understanding on how this hint operates.



To influence costing and cardinality estimates by the optimizer in the hope that a better execution path is selected. This hint supersedes the “CARDINALITY” hint.


Parameters separate by space (optionally by comma). “[]” means that “query block” is optional.

/*+ OPT_ESTIMATE( [query block] operation_type identifier adjustment ) */

  • query block

    optional and in the format “@QB_NAME”. For example, in the lack of any QB_NAME hints, you could say: OPT_ESTIMATE(@SEL$1 TABLE….)

  • operation_type


    refers to table


    select /*+OPT_ESTIMATE(TABLE DD ROWS=10)*/
    count(*) from dual DD

    adjusts index filtering – number of rows produced by the index lookup, does not affect the cost to scan the index

    Example INDEX_FILTER – changes output rows ONLY:

    select /*+ OPT_ESTIMATE(INDEX_FILTER C CKK$ID ROWS=1234) */*
    from ckk c  where id > 9000000

    For index range or skip scans – how many rows will be scanned in the index – affects the COST of the index access and the number of rows outputted

    Example INDEX_SCAN – changes COST and ROWS:

    select /*+ OPT_ESTIMATE(INDEX_SCAN C CKK$ID ROWS=1234) */*
    from ckk c  where id > 9000000

    the join result of 2 tables – oracle joins 2 tables at a time. Note that you can adjust join cardinalities between 2 tables in order to make that join not to be used.

    select /*+ OPT_ESTIMATE(JOIN (C S) ROWS=123456) */*
    from ckk c join small s on ( =
  • identifier

    qry_block is always optional

    TABLES: [email protected]_block

    INDEX: [email protected]_block index_name

    JOINS: JOIN([email protected]_block [email protected]_block)

  • adjustment


    SCALE_ROWS=number – multiplier to be used. For example 0.1 is 10 times less rows than originally estimated

    MIN=minimum number of rows from step – very useful to prevent ‘insane’ plans

    MAX=maximum number of rows from step


2013 April 17: Added: Examples for INDEX_FILTER, INDEX_SCAN and added one more reference with examples
2012 Dec 06: Added MIN/MAX description
2012 June 21: Added References


These are sites that gave me syntax details, or usage details. The examples and experiments however are my own.

SQL Profiles (10g)
Trivial Research on the Cardinality Feedback on 11gR2

Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

An Oracle ACE with a deep understanding of databases, application memory, and input/output interactions, Christo is an expert at optimizing the performance of the most complex infrastructures. Methodical and efficiency-oriented, he equates the role of an ATCG Principal Consultant in many ways to that of a data analyst: both require a rigorous sifting-through of information to identify solutions to often large and complex problems. A dynamic speaker, Christo has delivered presentations at the IOUG, the UKOUG, the Rocky Mountain Oracle Users Group, Oracle Open World, and other industry conferences.

9 Comments. Leave new

Good. I’ve been waiting for someone to make a list for this hint.

Thanks for the work.

MuJiang Charlie
July 12, 2010 5:22 pm

e.g. This doesn’t work on my 11.2 database,

select /*+ OPT_ESTIMATE(INDEX_SCAN, D4, PK_DEPT, ROWS=555 ) */
from scott.dept D4
where deptno > 0;

SELECT * FROM table(DBMS_XPLAN.DISPLAY_cursor(format => ‘TYPICAL’ ));


Christo Kutrovsky
April 17, 2013 2:45 pm

Hi Charlie,

Just updated blog with some examples that I confirmed are working.

Hints of Acceptability « OraStory
December 12, 2011 4:48 pm

[…] OPT_ESTIMATE (Undocumented but useful link) […]

Uwe Küchler
March 26, 2015 1:29 pm

Thank you for that Roundup! It saved my day today when I troubleshot a problem involving a table function within a package. I didn’t want to take the risk to deploy a statistics type and ASSOCIATE STATISTICS in production w/o further testing, so tuning the troublesome SQL with OPT_ESTIMATE was a good quick fix.

BTW: Meanwhile, there are a few documents on MOS that offer OPT_ESTIMATE as a workaround for bugs. But that certainly cannot be interpreted as an official support of that hint. :-(



Thank you for sharing. It’s very helpful. But is there any chances that it could by applied to estimate cardinality of the join of a nonmerged inline view and a table? I struggled to do this but with no success. The obvious solution to put synonym of the inline view as one of the tables’ names didn’t work for me. Neither did any other solution I crafted. Maybe I miss something simple.


Hi, Christo
I am runing query on the IOT table and hint is not working.
select /*+ OPT_ESTIMATE(INDEX_SKIP_SCAN dd IDX01_CONFIG_VALUE_NUMBER ROWS=1234) */ count(*) cnt from CONFIG_VALUE_NUMBER dd where value_number in (:p1);
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 2185 (100)| |
| 1 | SORT AGGREGATE | | 1 | 6 | | |
|* 2 | INDEX SKIP SCAN| IDX01_CONFIG_VALUE_NUMBER | 21 | 126 | 2185 (1)| 00:00:27 |
When table is not in the plan, hint does not work, if table is hint work.

select /*+ gather_plan_statistics OPT_ESTIMATE(INDEX_SKIP_SCAN dd IDX01_CONFIG_VALUE_NUMBER ROWS=1234) */
count(ts) cnt from CONFIG_VALUE_NUMBER dd where value_number in (:p1);

| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
| 0 | SELECT STATEMENT | | | | 2930 (100)| |
| 1 | SORT AGGREGATE | | 1 | 14 | | |
|* 2 | INDEX UNIQUE SCAN| CONFIG_VALUE_NUMBER_PK | 21 | 294 | 2930 (1)| 00:00:36 |
|* 3 | INDEX SKIP SCAN | IDX01_CONFIG_VALUE_NUMBER | 1234 | | 2185 (1)| 00:00:27 |

How to force a hint to work in the first case?



Interestingly the JOIN option doesn’t appear to work the way you would expect when joining views.

So this doesn’t work:

SELECT /*+ opt_estimate(JOIN (yy xx) ROWS=4) */
FROM dba_objects xx INNER JOIN dba_indexes yy ON (xx.owner = yy.owner);

while this does work:

SELECT /*+ qb_name(vw) opt_estimate( @vw query_block ROWS=4)*/
FROM dba_objects xx INNER JOIN dba_indexes yy ON (xx.owner = yy.owner)



from my colleague Havi:
The QUERY_BLOCK tag means that you refer to the actual, current SQL block.
That is: you are using the hint right there in the query block, and not outside of it.

count(*) from VAU;



Leave a Reply

Your email address will not be published. Required fields are marked *