Path: news.easynews.com!core-easynews!newsfeed1.easynews.com!easynews.com!easynews!c03.atl99!news.webusenet.com!news02.tsnz.net!newsfeed01.tsnz.net!ken-transit.news.telstra.net!news.telstra.net!newsfeeds.bigpond.com!not-for-mail From: "Richard Foote" Newsgroups: comp.databases.oracle.server References: <3eb1553f.1408630203@nyc.news.speakeasy.net> Subject: Re: Table locked while rebuilding index Lines: 50 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2720.3000 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2600.0000 Message-ID: Date: Tue, 6 May 2003 00:07:45 +1000 NNTP-Posting-Host: 203.54.191.122 X-Trace: newsfeeds.bigpond.com 1052142766 203.54.191.122 (Mon, 05 May 2003 23:52:46 EST) NNTP-Posting-Date: Mon, 05 May 2003 23:52:46 EST Organization: Telstra BigPond Internet Services (http://www.bigpond.com) Xref: core-easynews comp.databases.oracle.server:185308 X-Received-Date: Mon, 05 May 2003 07:05:45 MST (news.easynews.com) "Howard J. Rogers" wrote in message news:C__sa.28178$1s1.415446@newsfeeds.bigpond.com... > > "NetComrade" wrote in message > news:3eb1553f.1408630203@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