Re: Table locked while rebuilding index

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:C__sa.28178$1s1.415446_at_newsfeeds.bigpond.com...
>
> "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).
>

Hi Howard,

Ummmm, yes and no.

The table lock required by the rebuild is "queued" along with the other lock requests which means that yes, it is unlikely to be granted the lock immediately. The issue here (IMHO) is that it subsequently prevents other DML from acquiring a Share lock which can be somewhat painful.

But once it fronts it's enqueue, the rebuild can commence. It doesn't necessarily have to wait for the table "to be quiet" for it to gain the head of it's enqueue so it is very likely to acquire the lock to begin (or finish).

Just not straight away. And it's this "in queue wait period" that can have nasty repercussions.

Cheers

Richard Received on Mon May 05 2003 - 09:07:45 CDT