Re: When should one rebuild an index?

From: DA Morgan <>
Date: Tue, 30 Dec 2008 11:19:03 -0800
Message-ID: <>

Bob Jones wrote:
> "DA Morgan" <> wrote in message

>> Bob Jones wrote:
>>> I am not surprised you reached this conclusion based on only the example 
>>> above.
>> Rerun my demo and include any and all columns you wish. Then, using
>> those columns and the data they contain, make your case as to how it
>> constitutes sufficient information to determine an index should be
>> rebuilt.

> Sufficient information? Are we changing the topic again?

You may be ... I haven't wavered an angstrom.

> What a simplistic approach to tuning. If I can make a conclusion that
> INDEX_STATS is useless just base on a Mickey Mouse example without any other
> data, life would be easy. Anyone has a crystal ball to lend?

Perhaps you should reread this thread from the beginning.

To be honest, and I should be, I intentionally let this thread mislead the conversation just to see if anyone had actually used ANALYZE INDEX to make these decisions.

Given that I introduced a flagrant and obvious error the result to that question is clear.

The functionality that might be used is ANALYZE INDEX <index_name> COMPUTE STATISTICS. That no one noticed I used VALIDATE STRUCTURE says what needs to be said.

But, having run a full battery of tests using COMPUTE STATISTICS I will state, here and for the record, that it too provides a metrics that are not a reliable source of information as to whether an index would benefit from a rebuild.

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

Original text of this message