Re: 9i creating indexes with the ONLINE option

"Jeremy" <jeremy0505_at_gmail.com> wrote in message news:MPG.1f8aeaf42e683c0198a2ea_at_news.individual.net...
>
> Hi folks, anyone experienced any problems with creating indexes on table
> in 9iR2 specifying the ONLINE option?
>
> It doesn't *appear* to do as expected - i.e. allowing a new index to be
> created on a table whilst DML is occurring on the table on which the
> index is being built. All DML was suspended pending completion of the
> index creation when I tried this yesterday:
>
> create index mytab_n1
> on mytab (date_start) ONLINE
> tablespace ts_index;
>
> Are there some known bugs with this? Or am I missing something?
>
> --
> jeremy
>
> ============================================================
> ENVIRONMENT:
> Oracle 9iR2 / Oracle HTTP Server / mod_plsql / Solaris 8
> ============================================================

To do an online index build, Oracle does have to lock the table briefly at the start and end of the task.

This means the rebuild has to wait for all current transactions on the table to complete before it can start - and while it is waiting it blocks all new sessions that want to update the table.

Once current sessions complete, the rebuild starts and allows other sessions to work. But when the rebuild is nearly complete, it tries to take another lock - waits for current transactions on the table to commit, blocking new ones. When all the current transactions complete the rebuild completes, and the new transactions are unblocked. If you have some slow, or long-running transactions, this can be a nuisance.

-- 
Regards

Jonathan Lewis
http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis

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

Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html