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

On Dec 13, 9:59 am, hpuxrac <johnbhur..._at_sbcglobal.net> wrote:
> On Dec 13, 10:15 am, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net>
> wrote:
>
> snip
>
>
>
>
>
> > > > Look again, I posted an example proving the contrary to your claim
> > > > about empty table statistics being retained after the table has been
> > > > loaded.
>
> > > I did not make any such claim. You guessed about the possible
> > > existence of an unproven bug in 9206 on windows. I don't see any
> > > example proving that.
>
> > "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. "
>
> > You didn't prove that, and it implies empty table statistics being
> > retained or used after a table is loaded. Of course these tables were
> > never empty when statistics were generated, so I can't comprehend why
> > you insist upon beating that same horse over and over without any
> > proof to substantiate your claim.
>
> > David Fitzjarrell
>
> I think it implies at times the cost based optimizer can make bad
> guesses about efficient execution plans under certain circumstances
> such as statistics that no longer match the data contents. ( Picking
> the wrong index ... the wrong strategy ... etc ).
>
> One specific subcase of that is when the table was analyzed when it
> was empty.
>
> I am not sure what you are having such a hard time understanding.- Hide quoted text -
>
> - Show quoted text -

I have a difficult time understanding why you persist in this line of reasoning when you know, and it's been posted, that the tables never were empty when statistics were gathered. Your 'information' is useless here, so why attempt to justify your misguided advice?

David Fitzjarrell Received on Thu Dec 13 2007 - 11:38:53 CST