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

On Dec 7, 11:47 am, nicola.far..._at_info-line.it wrote:
> Patching this database is not an option, at least now (big customer,
> production database and so on)
> But we have other customers with 9206 on the same platform. I'll check
> next week how are their index statistics.
> I did a quick check now but they don't seem to have the same problem.
> They haven't compiled that specific table. They do have
> num_rows=0 etc in the user_indexes statistics but for tables that are
> actually empty (i checked only some. I'd need to write a script to
> check every table/index couple).
> On Metalink I haven't found anything about this, so I don't think
> about a bug here. Certainly I am missing something but .. what ??
>

Many people won't respond from cdos if you continue top posting. Posting at the bottom is much preferred ( or along the way ).

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.

So it comes down to the choice of getting statistics at the right time or trying anyhow or thinking about deleting the stats and using dynamic sampling. Received on Sat Dec 08 2007 - 19:03:29 CST