Re: When should one rebuild an index?

From: DA Morgan <>
Date: Fri, 26 Dec 2008 21:53:30 -0800
Message-ID: <>

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.

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 (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Fri Dec 26 2008 - 23:53:30 CST

Original text of this message