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$Ws1.6015@nlpi064.nbdc.sbc.com>
>>>>> Hi
>>>>> We can use
>>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE
>>>>> 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.
Date: Sat, 27 Dec 2008 16:53:59 -0600
Message-ID: <emy5l.15203$Ws1.6015@nlpi064.nbdc.sbc.com>
"DA Morgan" <damorgan_at_psoug.org> wrote in message news:1230405015.761378_at_bubbleator.drizzle.com...
> Bob Jones wrote: >> "DA Morgan" <damorgan_at_psoug.org> wrote in message >> news:1230357209.294829_at_bubbleator.drizzle.com... >>> Bob Jones wrote: >>>> <shweta.kaparwan_at_googlemail.com> wrote in message >>>> news:3bddc9d2-d04f-4525-9703-9c74ccd33d9e_at_h20g2000yqn.googlegroups.com...
>>>>> Hi
>>>>>
>>>>> We can use
>>>>> ANALYZE INDEX <index> VALIDATE STRUCTURE
>>>>> 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