Vagaries of the CBO: Out-of-Range Predicates

Hi,

Here is a seemingly simple situation. Database is 10.2.0.1.0 on AIX 5.3.

i have a query. Join of two tables (parent/child). CBO chooses the wrong index into the child table.

Parent table had 4 distinct tables.
Child table has 1 distinct value.

The first execution of this query passes a value that is not present (out-of-range value) in the child table.

The next execution passes a value that exists in the table, and as expected, oracle doesn't re-optimise the query - thanks to Shared SQL and probably Bind Peeking too.

there are no histograms on the table. only normal stats.

I found Bug No: 3663924, 5140413 which are the closest matches.

It says i need histograms for the column AND also setting the following event (if it is 9.2.x database),

SQL> alter session set events '38071 trace name context forever'; <https://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_id=3663924&p_database_id=BUG> But my table has an NDV of 1, so an histogram is not going to help.

So, i hacked the NDV to be greater than 2 and got CBO to use the right index.

My question is,

1. Is hacking the NDV the only way out in this situation where my NDV is 1 and there are no histograms?

Reading Jonathan's CBO bible, i find some good pointers spread across chapter 4 and 7. still pouring over it...

Oracle has some smart capability to compare the bind variables with those used to determine the execution plan. If the bind variables are too different, a new plan is calculated.

2) How does the CBO determine that a bind variable is different to another for the same query? is it just the datatype and size? there has to be something more than that.

thanks,
anand

--
http://www.freelists.org/webpage/oracle-l