RE: Index creation and locking

Thank you very much for the great details. I ran the index creation on Oracle 8.0.5.2.1, but I waited until the evening. The users weren't affected as far as I can tell.

Best regards,

Linda

-----Original Message-----
From: Tom Pall [mailto:tpall_at_cdproc.com] Sent: Saturday, August 12, 2000 11:14 AM To: Multiple recipients of list ORACLE-L Subject: 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 -----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Sent: Friday, August 11, 2000 7:47 PM

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

-- 
Author: Tom Pall
  INET: tpall_at_cdproc.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