Re: Table locked while rebuilding index

"NetComrade" <andreyNSPAM_at_bookexchange.net> wrote in message news:3eb1553f.1408630203_at_nyc.news.speakeasy.net...
> create index .. online;

The online creation or rebuilding of indexes is the biggest load of hooey Oracle's come up with since PCTINCREASE.

An online creation *still* requires an exclusive lock on the table to initiate the rebuild, and an exclusive lock on it to complete the process. If there are any outstanding transactions on the table when you issue the create or rebuild command, those DML statements already have a share lock on the table, and the rebuild/create process can't start, because it can't acquire the exclusive lock it needs. Your session just hangs, therefore, as a consequence. If it ever manages to get the exclusive lock, then fair enough: it releases it immediately. But the trouble is acquiring it in the first place.

All of which means that on a table that is subject to heavy DML, the index rebuild is rather unlikely to be able to acquire the lock it needs to begin (or finish).

And when would you like to use the online rebuild or create process? Er, on a table that's subject to a lot of DML (On the grounds that a table that has long periods of quiet can probably cope with ye olde offline rebuild/create perfectly well).

So you can use the online features when you don't really need to. And you can't reliably use them when you really, really need to!

Yuk.
Regards
HJR Received on Sat May 03 2003 - 21:45:32 CDT