Re: index rebuilding...

"Howard J. Rogers" wrote:

> <whole lot of snipping>
>
>
> 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
You have just brought up an interesting question I've never heard addressed. Tables and indexes are all just segments and a segment is a segment is a segment. With a table, if you empty a table with a delete there is a high water mark that when a full table scan takes place forces a read of every block. What happens with an index scan? Does it also read all of the empty index blocks? My guess is that it must because it has no way to know when to stop.

So while an index may begin becoming inefficient again after a rebuild, what doesn't, there is presumably a point at which there is a net benefit to a rebuild. And can we identify some guidlines around where that is?

Comments appreciated.

Daniel Morgan Received on Thu Jan 30 2003 - 16:34:20 CST