Re: Index creation and locking

It depends on your version of Oracle. If before Oracle 8.1, index creation places a share lock on the table, so that you can select it, but perform no update, delete, insert nor DDL against it.

In Oracle 8.1 you can do an online index build, which places a DDL share lock against the table (meaning no other DDL allowed against the table until the index is built). There is a very brief lock taken out against the table so Oracle can establish its baseline, then the index can be built online, while updates, inserts, deletes continue. When the index is done being built, another brief lock is made against the table while changes made to the table which affect the index are merged with the index. The index then goes from being a temporary segment to an index segment and all the locks are removed.

You will want to allocate as much sort_area_size for the session creating the index (things in memory happen in nanoseconds, on disk, milliseconds [a million times faster in memory]). You will also want to limit the index build to a time when the table has little DML if using 8.1 online build. Oracle suggests no more than 20% changes to the table while the index is being built. The less DML, the less extra logging of changes, the less merging at the end. Log off after building the index is you've increased sort_area_size, since in Unix the sort_area_size space used is offered back to Unix, but not accepted. It is freed up when the session logs off.

Building nologging will save time, as it minimizes redo generation.

If using Oracle 8.1, read the SQL Manual carefully for the correct syntax for online index rebuild.

If your are CPU or I/O bound already, scanning the table (if not fully cached), allocating/deallocating sort, rollback and index segments WILL slow things down even more, which will compete with other activity in the database. So once again, try to do this when the database is relatively quiet. ----- Original Message -----
From: Linda Hagedorn <Linda_at_pets.com>
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, August 11, 2000 7:47 PM
Subject: Index creation and locking

> Does index creation lock the underlying records or table for the duration of
> the creation, and subsequently interfere with other transactions, such as
> on-line?
>
>
> --
> Author: Linda Hagedorn
> INET: Linda_at_pets.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
Received on Sat Aug 12 2000 - 12:03:36 CDT