Re: How many people here rebuid index regularly?

MTNorman wrote:
> 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.
>

But why? I would expect the new data to reuse the space of the deleted data also in the index. Especially if you use the same key values. I could imaginge the index size will increase for a longer time period than the table but in the end, the index size shall remain as stable as the table. Also your argument is not about performance, it's only about disk space.

 From my experience index rebuilds are from the proactive-tuning-business which actually doesn't make sense at all. I never experienced a measurable performance gain by an index rebuild.

Jan Received on Fri Oct 05 2007 - 11:06:57 CDT