Re: CBO Predicate selectivity
- From: "K Gopalakrishnan" <kaygopal@xxxxxxxxx>
- To: Laimutis.Nedzinskas@xxxxxxxxxxxxx
- Date: Wed, 12 Jul 2006 23:03:33 -0700
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@xxxxxxxxxxxxx> 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..
Other related posts:
