Re: CBO Predicate selectivity

Hi,

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

Sorry for being dense, but how does changing num_rows change the selectivity ?

thanks
amit

Jonathan Lewis wrote:
>
>
> 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
>
>

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