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

  • From: Wolfgang Breitling <breitliw@xxxxxxxxxxxxx>
  • To: sac@xxxxxxxxxxxxx, oracle-l-bounce@xxxxxxxxxxxxx
  • Date: Wed, 28 Jun 2006 09:59:45 -0700

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@xxxxxxxxxxxxx:

>  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

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


Other related posts: