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

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. I am confident that test cases can be created either way showing reasonable and at times unreasonable execution plans.

> 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 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?
>
> Exactly! This is the crucial bit. Maybe your wording was too
> ambiguous.

??? Received on Mon Dec 10 2007 - 07:42:20 CST