Re: When should one rebuild an index?

Bob Jones wrote:
> "DA Morgan" <damorgan_at_psoug.org> wrote in message
> news:1230565383.240290_at_bubbleator.drizzle.com...

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

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
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org