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

From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Thu, 13 Dec 2007 18:12:55 +0100
Message-ID: <5sd7goF17laqmU1@mid.individual.net>

On 13.12.2007 16:59, hpuxrac 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.

The table was not empty when statistics were gathered. Period.

The interesting question here is not what plans the optimizer comes up with when statistics indicate an empty table but why it believes the table was empty when it wasn't during analysis.

        robert Received on Thu Dec 13 2007 - 11:12:55 CST