Re: create index on table with changing data

A copy of this was sent to "John F. Burrill" <burrill_at_sourceworks.com> (if that email address didn't require changing) On Mon, 20 Dec 1999 19:23:13 GMT, you wrote:

>We are trying to create an index on a table that is being
>accessed and updated continously via the web. When
>creating the index, the connecting processes build up
>and the create index takes so long that we have to kill it.
>
>How can we create the index without taking the database
>offline to users?
>
>Kindest regards,
>John F. Burrill.

You do not mention a version but in Oracle8i, release 8.1 you can use ONLINE in the create index:

ONLINE specifies that DML operations on the table will be allowed during creation of the index. For a description of online index building and rebuilding, see Oracle8i Concepts.

Restriction: Parallel DML is not supported during online index building. If you specify ONLINE and then issue parallel DML statements, Oracle returns an error.

this is new in 8i, not available in prior releases. In 8.0 and before, DML (but not queries) is prevented while the index is being built. You can use parallel index rebuilds,the unrecoverable option and so on to help speed it up.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Dec 20 1999 - 14:36:40 CST