Re: When should one rebuild an index?

shweta.kaparwan_at_googlemail.com wrote:
> 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.

I can think of a few worse criteria but you are reasonably close. As does Charles Hooper I highly recommend you read Richard Foote's notes.

-- 
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.org