Re: When should one rebuild an index?

Bob Jones wrote:

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

>>> "Robert Klemme" <shortcutter_at_googlemail.com> wrote in message 
>>> news:6rp8o6F2mq81U1_at_mid.individual.net...
>>>> On 28.12.2008 02:41, Bob Jones wrote:
>>>>
>>>>> I don't know what your definition of "meaningless" is here. How could 
>>>>> something be "meaningless" yet provides a specific piece of 
>>>>> information.
>>>> It provides a bit of data, whether that is information depends on the 
>>>> usefulness.  If you cannot do anything with it, it's just data.
>>>>
>>>> http://en.wikipedia.org/wiki/Data#Meaning_of_data.2C_information_and_knowledge
>>>>
>>> Hmmm, so it provides meaningless data about index structures and space 
>>> usage. I  wonder why Oracle went through all that trouble.

> 
> Index structure and space usage are meaningless in determining index 
> rebuild? So in your opinion what is meaningful? 

You were given a very good list of links earlier in this thread. Did you follow them? If not then I would suggest you start there.

There is no question in my mind that two best non-Oracle employee experts on the subject are Richard Foote and Jonathan Lewis. It would be far more valuable for you to read their opinions than mine.

What I hope you pay special attention to when you read their comments is that the value in rebuilding an index has something to do with the efficiency of storage but that is only one consideration out of many. Also critically important is whether the index, by the nature of how it is being used (or misused), will return to that same inefficient state almost immediately. If it will then an index rebuild is going to be worthless no matter the metrics. And this critical piece of information can not be found within any one tool or technique.

Part of being an Ace is knowing that you don't know everything but, hopefully, knowing enough experts that you can find out what you don't know when questions arise. The most valuable part of being an Ace to me is my relationship with other Aces and the many Oakies I've met over the years. Just this year, here in Seattle, we have had visits from Tom Kyte, Mogens Norgaard, Cary Millsap, Kevin Closson, Jeremiah Wilton, Kyle Hailey, Richard Foote, Karen Morton, Hans Forbrich, Tim Tow, and Ed Roske. No single person is "that" smart. Everyone working together smart enough. We all have things we can learn from others if we are receptive.

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