Re: How many people here rebuid index regularly?

When the deletion pattern creates relatively sparse clusters of deleted leafs, then the leaf blocks are not likely to be reused and the index block itself becomes fragmented (think delete 2 skip 3, then insert 4 later with keys related to the delete 2 recs). New leaf blocks are added for the insertions and the total index size just keeps extending.

Recently in a 9.2.0.6 database where the application attempts to keep only the last 4 days of completed orders online, index sizes dropped an average of 50% with an average performance improvement of 150%. Please note that "orders" enter the system in batches and stay in the systems for 4 days after they have completed; however, most of the orders loaded on day 1 are not deleted on day 5 or even by day 10. It's the widely spread deletion pattern of small numbers of records (particularly when the deletion order does not correlate to the insertion order) that creates an index that benefits from rebuilding. Other customers using the same application code with a dense insertdeletion  pattern (50% of day 1 inserts are deleted on day 5) do not experience the performance drop off.

So if the index avg row length is a fraction of the table avg row length and the index storage is still a multiple of the table storage (30 bytes on the index, 200 bytes on the table, 10g for the table, 20g for the index) - you may want consider reindexing. The ratio does not necessarily mean rebuilding is needed - it's just a tool/method for identifying indexes to examine. A better method may be segment activity - fragmented indexes that are causing performance problems in OLTP also tend to be among the most active segments.

On Oct 5, 12:06 pm, Jan Krueger <j..._at_stud.uni-hannover.de> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -
Received on Mon Oct 08 2007 - 14:56:57 CDT