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

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. Received on Thu Dec 13 2007 - 09:59:15 CST