RE: full-scan vs index for "small" tables

What would you replace it with? The selectivity for dependent / correlated predicates can range anywhere from 0, i.e. the predicates are never both true at the same time, such as in Mark's example, or at the other end of the scale, they could be completely correlated such that when one is true, the other is true as well, so the extra predicate does not really add any selectivity. There are ways to combat that. None is a cure-all. The CBO does some sanity checking. One possibility is to create an index on the correlated columns. The CBO can then use the index' distinct keys statistic to do sanity checking.
Another way is to enable optimizer_dynamic_sampling with a level >= 4 either on the session, or via a hint on the sql. System wide is probably not a good idea. And you can use the statistics to manipulate the selectivity calculation of the combined predicates. That is tricky and not always possible since it alters the selectivity of one or both predicates, but in special, yet frequent (in Peoplesoft) cases I have successfully used that.

Quoting oracle-l-bounce_at_freelists.org:

> Are any of the CBO assumptions (like the Predicate Independence
> assumption below in Mark's example) modifiable by parameters? I am
> merely tossing this out - I am not sure if this would wreak havoc or
> not. =) A curiosity.
>
> -charles schultz
>

-- 
regards

Wolfgang Breitling
Oracle 7,8,8i,9i OCP DBA
Centrex Consulting Corporation
www.centrexcc.com

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