Howard J. Rogers wrote:
> "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
>
>
>
I hoping that this rebuilding will be a rare occasion Howard, in this
case I mostly want to set the storage params to something resembling
normal, and at the same time move them to a more suitable tablspace,
perhaps as Frank suggested...
cheers!
Received on Thu Jan 30 2003 - 14:54:02 CST
