RE: CBO Predicate selectivity

>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.

Confirmed. Lowering rows number on the driving table (on the sys-index as well because it is IOT table) had the best effect so far. Some index cardinalities changed to the right direction causing index scan on date column.

But the cardinality of IOT index stays the same and it is causing some other problems. It seems as if Oracle may be performing some sanity checks using the unmodified indexes [statistics.]

Brgds,
Laimis N.  

-----Original Message-----
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jonathan Lewis Sent: 13. júlí 2006 08:11
To: oracle-l_at_freelists.org
Subject: Re: CBO Predicate selectivity

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 normal.

Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

• Original Message ----- From: "FreeLists Mailing List Manager" <ecartis_at_freelists.org> To: "oracle-l digest users" <oracle-l_at_freelists.org> 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_at_landsbanki.is>
>
> 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
> Fyrirvari/Disclaimer
> http://www.landsbanki.is/disclaimer
>
>

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


Fyrirvari/Disclaimer
http://www.landsbanki.is/disclaimer
--
http://www.freelists.org/webpage/oracle-l