Re: How is possible that avg_data_blocks_per_key = 0 even with fresh statistics ?

On Dec 10, 9:12 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote: snip

> > Well this is going off course again but since you brought it back up.
>
> > No actually what I intended to say was exactly what I said. The
> > optimizer may or may not come up with bad plans if the statistics say
> > that a table is empty when it is no longer in that condition.
>
> Again you assume the table was empty at some point when statistics
> were collected; according to the OP this was never the case. So why
> do you insist on pursuing this line of 'logic'?

Read what Mr. Robert Klemme posted ... that's what I responded to there. Not from the OP.

>
> > I am
> > confident that test cases can be created either way showing reasonable
> > and at times unreasonable execution plans.
>
> Two entirely separate statements. Yes, Jonathan Lewis has generated
> several such cases but none illustrating the phenomenon you insist on
> foisting on people. But the optimizer creates these bad plans based
> upon existing statistics notably due to such things as poorly
> calculated clustering factors, not zero-valued index 'statistics' for
> non-zero valued table stats.
>
> Of course you've not supplied examples for either case.

I don't see that you have either.

> The OP has proven this by posting the TABLE statistics along with the
> associated INDEX statistics. Maybe you've missed that part of the
> discussion.

Look at post 24 in the topic thread. Received on Thu Dec 13 2007 - 07:41:31 CST