Re: ANALYZE - Currupting Index

>Hans Forbrich wrote:
>>
>> Personally I've never run across Analyze corrupting an
>> index (doesn't mean it can't hapopen - just never seen it).
>>
>>
>> After analyzing, try dropping the stats to see if you
>> get improvements.
>>
>> /hans
>
>True!
>
>The CBO will kick in if ANY table in the query has stats.
>
>If you have a multi-table join and analyze only one index Oracle will have to estimate stats for
>everything else.
>
>If you are going to use the CBO you should make sure ALL stats are up to date.

The problem here is

ANALYZE TABLE <tablename> VALIDATE STRUCTURE; which does not populate the user_tables.

and NOT

ANALYZE TABLE <tablename> COMPUTE/ESTIMATE STATISTICS;

I have found queries to be faster after the ANALYZE TABLE <tablename> VALIDATE STRUCTURE command.

Anyone out there thinks otherwise.

Naren Chintala
naren_at_mink.att.com Received on Mon Oct 07 1996 - 00:00:00 CEST