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

Comments embedded.
On Dec 10, 7:42 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Dec 10, 7:54 am, Robert Klemme <shortcut..._at_googlemail.com> wrote:
>
> > On Dec 9, 10:53 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
>
> snip
>
> > I don't understand why then you write this, which I believe is the bit
> > that David refers to:
>
> > > The optimizer at times can get confused with tables and indexes that
> > > were analyzed when they were empty ( num_rows =0 etc ) but now have
> > > data loaded. As best I understand it almost any plan the optimizer
> > > might come up relating how to access an empty table may look similar
> > > so it can pick the wrong index, use full scan, a strange type of index
> > > access etc.
>
> > Maybe you intended to say that the optimizer comes up with bad plans
> > if it *thinks* the table is empty, which would fit the phenomenon much
> > better.
>
> 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'?

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

>
> > But I believe the real issue here is why *does* the optimizer
> > think it is empty in the first place although the table was analyzed
> > when not empty?
>
> That seems to be what the OP is claiming.

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.

>
> > > The OP is stating that he has a system where the table was analyzed
> > > and it was not empty at the time but values of 0 appear in the index
> > > stats. Are you not understanding that part?
>

I understand that part quite well. It appears you've missed that boat somehow.

> > Exactly! This is the crucial bit. Maybe your wording was too
> > ambiguous.
>
> ???

David Fitzjarrell Received on Mon Dec 10 2007 - 08:12:01 CST