Re: index rebuilding...

"Ryan" <rgaffuri_at_cox.net> wrote in message news:3EXZ9.53855$GX4.2199677_at_news2.east.cox.net...
> I could have swarn I read something either in Tom Kyte's book or on his
site
> stating that rebuilding indexes is a bad idea?

You would have done. Tom claims (or, I should say, once told me) that he's only ever rebuilt an index about 8 times in his working life.

>I saw something about how an
> index will eventually get to a state of equilibrium and you may experience
> slowdowns while it reestablishes that equilibrium.
>
> you know any documentation on this? about when to rebuild indices and
such?

You might also find a snippet on this subject in 'Beginning Oracle Programming' (Wrox) -end of shameless plug- in the quite brilliantly-written chapter on Indexes. Jonathan Lewis also has some advice on the matter in his Practical 8i book.

The point Tom was making, and one I did some testing on, is that an index may grow and grow and grow, and in the process acquire a rather large number of extents, many of which may contain bits of empty space as a result of updates and deletes on the associated table. If you rebuild the index, then sure enough the index will be compacted down to a smaller size (and hence fewer extents). Great. Except that presumably DML on the table doesn't suddenly come grinding to a halt, so that the very next bit of DML that happens on the table will cause the index to start getting bloated again. And (here's Tom's particular point) as the index starts getting bloated again, it has to *re*acquire the extents which it had already gone to the trouble of acquiring before the rebuild. In dictionary-managed tablespaces, extent acquisition can be an expensive process (especially if there's contention for the UET$ and FET$ dictionary tables which are involved). Therefore, performance can actually slow down after an index rebuild, because you have extent allocations to deal with. The index itself also has to re-organise itself as DML takes place on the table, with block splits and so on. Before the rebuild, there was plenty of empty space into which new entries could be fitted without inducing heaps of cascading re-organisation. With a freshly-compacted index, re-organisation can be expensive. I confess that most of my testing indicated not much performance degradation after a rebuild, but some of it did. So it can happen.

Jonathan makes much the same point in his book: it's daft to constantly rebuild indexes in pursuit of a mythical 100% efficiency, because continuing DML on the table means that the nanosecond you achieve 100% efficiency, you start losing it again (fresh block splits etc etc etc).

There's also the fact that a hoary old myth still exists that Oracle never re-uses deleted space in an index. But that's just not true. If you delete from EMP where name='KELLY', and then insert into emp (name) values 'KIM' then you will indeed (probably) re-use the index entry space for the dead KELLY entry. Meaning that your second insert does NOT have to induce a block split, and that an analysis of the index would reveal zero deleted leaf rows. In other words, Oracle uses index space rather more efficiently than many people think, and as a result the initial *need* for an index rebuild is rather lower than many people think.

Tom went so far as to say that 'only truly degenerate indexes' should ever need to be rebuilt. I wasn't so much of a true believer, I confess, so I watered that down to 'an occasional rebuild' *might* be appropriate.

Bearing in mind that even online rebuilds take exclusive table locks at the beginning and end of the rebuild (and an offline rebuild takes an exclusive lock for the duration of the rebuild, of course) then the costs of rebuilds can be considered large, and so you are weighing a small benefit of increased efficiency (which, as explained above, might not be much of a benefit in any case) versus a big drawback of exclusive locking, lots of I/O and so on.

Net result: rebuilds should be rare.

Regards
HJR Received on Thu Jan 30 2003 - 13:35:15 CST