Re: alter index rebuild (NOT ONLINE)

Norman Dunbar <Norman_at_RE-MO-VE.BountifulSolutions.co.uk> wrote in message news:<pan.2003.11.01.16.21.39.830796_at_RE-MO-VE.BountifulSolutions.co.uk>...
> > Let's say the original poster's
> > database is supposed to be read-only but analysts find incorrect
> > data (this does happen sometimes). Updates or deletes are run and
> > the database comes back to read-only. Then it may be worth
> > rebuilding some huge indexes to save disk space and index scan time.
>
> True, but I would imagine that any deletions will be reused by insertions
> or updates (to the indexed columns anyway) so maybe no rebuild is
> required. On the other hand, if there were huge amounts of data deleted
> then it is possible that complete index blocks were also emptied, in which
> case, they will be added back to the freelist(s) - again, no rebuild
> required. As ever, 'it depends' :o)
>
> Tom Kyte claims only to have rebuilt 8 indexes in his career, so that's
> really how often indexes *need* rebuilding.

Norman,

What you describe is quite correct. Tom Kyte has a very impressive analogy. An index is like an ordinary person; if you force yourself to lose fat, you'll get a little fatter back to what you are now. Similarly, if you force an index to be unnaturally skinny (densely packed), it'll still become plump as it is now.

The case I described is such that the next data load may come a few weeks or even months down the road. Before that time, many users need to query the data. In this case, it makes sense to force the index to be "unnaturally" skinny by rebuilding or coalescing it, if I can. The less data blocks for the index, the less disk I/O and more efficient use of buffer cache. In practice, though, you need try at least once to see if the index size really comes down.

Given that Tom has been in this business for so long, it's amazing he still remembers the exact number of times he rebuilt indexes...

Yong Received on Sun Nov 02 2003 - 15:43:22 CST