Re: Freezing database statistics

On Oct 31, 4:25 am, dbaplusp..._at_hotmail.com wrote: <snip>
> > select *
> > from orders
> > where order_date > sysdate - 1
> > ;
>
> > The SQL doesn't change, but if you freeze the statistics,
> > and keep taking orders, the CBO will eventually decide
> > that there are no order matching the requirement. (At
> > least, that's will be true from 10g onwards - earlier
> > versions treat most manipulations of sysdate as if it
> > were a bind variable, which would results in a 5% selectivity
> > in the example above).
>
> > --
> > Regards
>
> > Jonathan Lewis
> >http://jonathanlewis.wordpress.com
>
> > Author: Cost Based Oracle: Fundamentals
> >http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> > The Co-operative Oracle Users' FAQ
> >http://www.jlcomp.demon.co.uk/faq/ind_faq.htmlI am not sure whether I understand you. How can using or not using
> statistics can give wrong result. Result may be slower but eventually
> all the orders in table whose order_date > sysdate - 1 will be shown.
> Please elaborate.

Oracle won't (or rather if it does its a bug) get the wrong result, however it may decide upon a pretty bad execution plan because it doesn't know what the spread of the data is.

cheers

Niall Litchfield
Oracle DBA
http://www.orawin.info/services Received on Tue Oct 31 2006 - 06:48:52 CST