Until 10g, the optimizer does not peek at values for this query - it uses the standard col >/< :bind algorithms which basically means the predicate has a selectivity of 0.25% (5% of 5%).
- "where date between to_date(:b1) and to_date(b2)"
The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Subject: CBO Predicate selectivity Date: Wed, 12 Jul 2006 08:44:18 -0000 From: "Laimutis Nedzinskas" <Laimutis.Nedzinskas@xxxxxxxxxxxxx>
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.
Thank you in advance, Laimis