Re: CBO Predicate selectivity

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 13 Jul 2006 09:10:56 +0100

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)"

You say you can't change the query, so if you want to fake this query, you probably need to use dbms_stats.set_table_stats
to tell Oracle that the table has a very small number of rows -
but don't change the block count - so that a properly calculated
selectivity is fooled into getting the right cardinality. Then capture
the execution plan in a stored outline - and put the stats back to


Jonathan Lewis

The Co-operative Oracle Users' FAQ

Cost Based Oracle: Fundamentals

----- Original Message ----- From: "FreeLists Mailing List Manager" <ecartis@xxxxxxxxxxxxx>
To: "oracle-l digest users" <oracle-l@xxxxxxxxxxxxx>
Sent: Thursday, July 13, 2006 7:05 AM

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

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

Thank you in advance, Laimis


Other related posts: