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

On Dec 9, 10:53 pm, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Dec 9, 3:29 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
>
> snip
>
>
>
> > You keep harping on this table being empty when stats are gathered,
> > and it's been said more than once by the OP that this is not the
> > case.
>
> In a different post I noted that I had mis-understood that part. Why
> don't you read that post?

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

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

Regards

robert Received on Mon Dec 10 2007 - 06:54:58 CST