Re: Freezing database statistics

Mladen Gogala wrote:
> On Mon, 30 Oct 2006 20:25:53 -0800, dbaplusplus wrote:
>
> > I 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.
>
> The trick is really simple: if you have histogram on the column order_date
> and do not update statistics, then sysdate-1 will become larger then the
> maximum value in the index, as far as CBO is aware, because CBO gets its
> maximum value from the histogram. CBO just might return an empty set if
> you have inaccurate statistics. This is a wonderful case that I haven't
> really thought of. Brilliant, Jonathan! I'll have to test it, it is not
> hard to construct a test case now that I know the trick. Of course, this
> fouls up any hope of plan stability without outlines.
>
> --
> http://www.mladen-gogala.com

Does not this sound like a bug in Oracle 10g?

If one does not gather histogram statistics at all, one should not run into this. Received on Tue Oct 31 2006 - 06:42:02 CST