Re: How many people here rebuid index regularly?

My experience is that indexes generally do not need to be rebuilt unless:
(1) I want to empty the tablespace (don't really need this anymore with 10g), or
(2) we have an application that is _very_ delete intensive.

Delete intensive applications are usually trying to keep only a certain volume of data in the active database and have some process that deletes data on a frequent basis. Another type of app "reloads" data with the same key values but some additional data columns populated in later versions/data sets - instead of updates or merges - the code deletes and then inserts. In both of these cases, you will see the amount of space used by the index increase steadily while the table size remains relatively stable. Rebuilding the index results in a smaller index and improved performance on queries that use the index.

On Oct 4, 10:10 pm, "charlie cs" <charliecs(nospam)@hotmail.com> wrote:
> We are on 9i Oracle.
>
> Search on the internet, some expert say
> "In the overwhelming majority of cases, indexes are extremely well
> self-maintained and index rebuilds are NOT required, ever."
>
> Tom Kytes in his website says
> "The time lag between index rebuilds should be approximately FOREVER.
> Tell me -- why why why WHY are you rebuilding them on a schedule? What is
> the scientific
> basis in reality that is driving you to do this???? "
>
> And some other site says:
> "First rule of thumb is if the index has height greater than four, rebuild
> the index. For most indexes, the height of the index will be quite low,
> i.e. one or two. I have seen an index on a 3 million-row table that had
> height three. An index with height greater than four may need to be rebuilt
> as this might indicate a skewed tree structure. "
>
> Which one is right?
>
> Thanks for your comments
>
> Thanks
Received on Fri Oct 05 2007 - 10:32:20 CDT