Re: When should one rebuild an index?

From: DA Morgan <>
Date: Sat, 27 Dec 2008 11:10:16 -0800
Message-ID: <>

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.

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.

Daniel A. Morgan
Oracle Ace Director & Instructor
University of Washington (replace x with u to respond)
Puget Sound Oracle Users Group
Received on Sat Dec 27 2008 - 13:10:16 CST

Original text of this message