I do know that it is not a good idea to rebuild indexes. I was only
going to do it because developers keep building them in tablespaces
other than the index tablespace, so I just wanted to move them.
I have read many places that it is not a good idea to rebuild them,
and I believe that. It is odd though, that Oracle does recommend it:
---
on page 13-36 in my book "Enterprise DBA Part 2:Performance Tuning
Workshop" (That I got from a class at Oracle University), it says as
the first sentence, "You should regularly rebuild your indexes."
In my book "Oracle 9i DBA Handbook" (from Oracle Press), on page 149,
the last paragraph says "To reclaim the unusable space in an index,
you can use the ALTER INDEX REBUILD command. Schedule a batch job to
run periodically to rebuild the indexes on your most active tables."
---
Thanks for the clarification. These index rebuilds are very quick, so
I am not too worried if the application hangs for 2 seconds. I just
don't want things to crash or become corrupt.
> An index rebuild (even the online kind) takes an exclusive lock on the
> table. If DML is already happening on the table, the rebuild command itself
> hangs. If the rebuild acquires the lock, then subsequent DML on the table
> hangs.
>
> The difference with the online rebuild is that having acquired the lock, it
> then releases it, so the length of time the table is locked is kept to a
> minimum... though it has to re-acquire the lock to complete the rebuild, so
> there's another opportunity for locking and hanging. The problem with the
> online rebuild (bugs aside) is that if it can't acquire the exclusive lock
> in the first place (because of already-happening DML on the table) it hangs.
> And then subsequent DML requests queue up behind the DDL command, appearing
> to hang). So even the online rebuild is not terribly "online".
>
> Hence the generic advice: indexes seldom need rebuilding. However you do it,
> it's expensive, ruins concurrent access to the table, involves a lot of I/O
> and is usually not needed in the first place.
>
> Regards
> HJR
Received on Thu Oct 30 2003 - 11:24:23 CST
