Thanks that solve the issue.
Seems like I need to go through dbms_stats options in details.
Randolf Geist wrote:I'm not a DBA so don't know how and hwere to look for 10053 optimizer trace debug file.
One question that remains is why the optimizer didn't choose the index when you correctly used the dynamic sampling hint. The estimated cardinality of 552 should have lead to an execution plan that uses the existing and available index. That looks odd and would probably need a look into the 10053 optimizer trace debug file, but is obviously now only a side note since your issue seems to be resolved.
I'm not a DBA so don't know how and hwere to look for 10053 optimizer trace debug file.
But, if you tell me where and what to look I'll try to find it.
Though, problem is resolved, I would like to go to the root of issue.
3. Restore the current statistics using DBMS_STATS.RESTORE_TABLE_STATS or simply re-gather with the options that worked for you.
ALTER SESSION SET TRACEFILE_IDENTIFIER = '10053_optimizer_trace'; ALTER SESSION SET EVENTS '10053 trace name context forever, level 1'; EXPLAIN PLAN FOR <your_statement>; ALTER SESSION SET EVENTS '10053 trace name context off';