Re: Table locked while rebuilding index

And another interesting detail to devalue ONLINE -

It looks like online rebuilds have to scan the table, whereas 'offline' rebuilds can scan the existing index - potentially reducing the initial I/O costs quite significantly. (Of course, both options seem to require a sort at the end, which may make the difference between a tablescan and an index ffs immaterial).

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr


One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

____Denmark__May 21-23rd
____Sweden___June
____Finland__September
____Norway___September

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK_(Manchester)_May x 2
____Estonia___June (provisional)
____Australia_June (provisional)
____USA_(CA, TX)_August

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


"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).

>

> 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

>

>

>