Re: alter index rebuild (NOT ONLINE)

<OakRogbak_erPine_at_yahoo.com> wrote in message news:13fdc9b4.0310290852.145fddf7_at_posting.google.com...
> What is the consequence of doing an ALTER INDEX IDXNAME REBUILD
> TABLESPACE INDX2;
> in an Oracle 8.1.7.0.0 database if that table is be used (possible
> DML)? Would an update statement fail or hang until the index is
> finished moving and rebuilding?
>
> I don't want to do the ONLINE rebuild until I patch my db due to the
> 1475310 bug?
>
> Thanks!

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 Wed Oct 29 2003 - 13:32:33 CST