Re: CBO Predicate selectivity

Laimutis,

Not sure whether Jonathan's book addresses this issue.. The note is correct on the default selectivity of the bind is 5%. However that is one of the wrong assumption by the CBO and most often backfires.

If you want to ask the CBO to consider the index, there is an underscore parameter where you ask the CBO to evaluate that like predicate as equality predicate when there is a bind.. The parameter is _like_with_bind_as_eqality.

Good Luck..

Gopal

On 7/12/06, Laimutis Nedzinskas <Laimutis.Nedzinskas_at_landsbanki.is> wrote:
>
> Back to the basics.
>
> I need any information on how CBO calculates [predicate] selectivity
> which is more precise than in the Note:68992.1
>
> This note states that "c1 > :bind1 Default of 5%" which I
> doubt very much. At least it is not clear how distinct values/min-max
> values/datatype contribute to those 5% but the they do seem to
> contribute.
>
> As a whole, CBO uses some heuristics (see Note:212809.1) which I need to
> tweak by manipulating statistics for one simple reason:
>
> - I have a legacy query "where date between to_date(:b1) and
> to_date(b2)" => this query can not be modified.
>
> - I know what CBO does not know: an index on date column must be used
> because selectivity of "date between to_date(:b1) and to_date(b2)" is
> high.
>
>
>
>

-- 
Best Regards,
K Gopalakrishnan
Co-Author: Oracle Wait Interface, Oracle Press 2004
http://www.amazon.com/exec/obidos/tg/detail/-/007222729X/
Coming soon.... Oracle RAC Handbook...Oracle Press 2006..

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