Re: When should one rebuild an index?
From: DA Morgan <damorgan_at_psoug.org>
Date: Fri, 26 Dec 2008 21:53:30 -0800
Message-ID: <1230357209.294829@bubbleator.drizzle.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: Fri, 26 Dec 2008 21:53:30 -0800
Message-ID: <1230357209.294829@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.
If you truly want to know when to rebuild indexes read Richard's comments. Read Jonathan Lewis' comments. And learn wield SYS_OP_LBID.
Tom Kyte finally managed to make an impact with respect to bind variables after carrying the torch for it seems like a decade. Can anyone put this index rebuild nonsense into the rubbish bin where it belongs? Please!
-- Daniel A. Morgan Oracle Ace Director & Instructor University of Washington damorgan_at_x.washington.edu (replace x with u to respond) Puget Sound Oracle Users Group www.psoug.orgReceived on Fri Dec 26 2008 - 23:53:30 CST