Re: When should one rebuild an index?

From: Bob Jones <email_at_me.not>
Date: Sat, 27 Dec 2008 16:53:59 -0600
Message-ID: <emy5l.15203$>

"DA Morgan" <> wrote in message

> Bob Jones wrote:
>> "DA Morgan" <> wrote in message 
>>> Bob Jones wrote:
>>>> <> wrote in message 

>>>>> Hi

>>>>> We can use
>>>>> command on the affected indexes - each invocation of this command
>>>>> creates a single row in the INDEX_STATS view.
>>>>> Thus determining the indexes which are good candidates for rebuilding.

>>>>> BUT my doubt is that if,
>>>>> we use dbms_stats to gather the statstistics for CBO using cascade
>>>>> option. Then if
>>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE is ran , that what will be
>>>>> fate of the stattistics gathered using dbms_stat for index( cascade
>>>>> option).

>>>>> Also, analyze command is deprecated now. So is there any other way to
>>>>> find out the indexes which needs rebuilding.?

>>>>> Thanks.
>>>> INDEX_STATS is populated by ANALYZE only. Your CBO data collected by 
>>>> DBMS_STATS are safe.
>>> The CBO data is safe but what you are suggesting as criteria irrelevant.
>> What criteria?
> I will state it clearly.
> The data collected by ANALYZE does not lead to a conclusion as to
> whether an index should be rebuilt. In other words ... the fact that
> CBO data is not destroyed is irrelevant. Time is being spent, CPU and
> I/O wasted, producing a metric that is not a relevant criteria.

It doesn't look like OP has reached any conclusion yet. He was merely asking a question. I think my response was relevant to his question. Whether he should rebuild indexes based on the stats and other factors is not my call.

> This is not different from the same mythologist promoting Buffer Cache Hit 
> Ratios. Yes you can generate a value. A numeric value. But valid
> conclusions can not be drawn from that numeric value. Only, perhaps,
> about the person that suggested making a decision based upon it.

Not different in the way BCHR is also irrelevant to performance and it is just a number? That's fascinating.

Perhaps, instead of looking at stats, we just need an Ace Director to orchestrate DBAs what to use or not to use in all situations. Received on Sat Dec 27 2008 - 16:53:59 CST

Original text of this message