The ONLINE keyword of CREATE INDEX

The DBA can reduce the risk of nonexclusive access errors, and can increase the availability of the indexed table, by including the ONLINE keyword as the last specification of the CREATE INDEX statement. The ONLINE keyword instructs the database server to create the index while minimizing the duration of an exclusive lock, so that the index can be created while concurrent users are accessing the table.

By default, CREATE INDEX attempts to place an exclusive lock on the indexed table to prevent all other users from accessing the table while the index is being created. The CREATE INDEX statement fails if another user already has a lock on the table, or is currently accessing the table at the Dirty Read isolation level.

The database server builds the index, even if other users are performing Dirty Read and DML operations on the indexed table. Immediately after you issue the CREATE INDEX ONLINE statement, the new index is not yet visible to the query optimizer for use in query plans or cost estimates, and the database server does not support any other DDL operations on the indexed table, until after the specified index has been built without errors. At this time, the database server briefly locks the table while updating the system catalog with information about the new index.

The indexed table in a CREATE INDEX ONLINE statement can be permanent or temporary, logged or unlogged, and fragmented or non-fragmented. You cannot specify the ONLINE keyword, however, when you are creating an index that has any of the following attributes:
  • a functional index
  • a clustered index
  • a virtual index
  • an R-tree index
  • an index that is partitioned by an interval fragmentation strategy
  • an index on a table that is partitioned by an interval fragmentation strategy.

In addition, if a primary key constraint is defined on the table, a CREATE INDEX ONLINE operation can generate error -710 if one or more concurrent sessions are performing DML operations on a child table that has a foreign key constraint referencing that primary key. Before the index can be created ONLINE, you must wait until all the user sessions with those child tables have completed.

The following statement instructs the database server to create a unique online index called idx_1 on the lname column of the customer table: 
CREATE UNIQUE INDEX IF NOT EXISTS idx_1 ON customer(lname) ONLINE;

If, while this index is being constructed, other users insert into the customer table new rows in which lname is not unique, the database server issues an error after it has created the new idx_1 index and registered it in the system catalog.

The term online index refers to the locking strategy that the database follows in creating or dropping an index with the ONLINE keyword, rather than to properties of the index that persist after its creation (or its destruction) has completed. This term appears in some error messages, however, and in recovery or restore operations, the database server re-creates as an online index any index that you created as an online index.

No more than one CREATE INDEX ONLINE or DROP INDEX ONLINE statement can concurrently reference online indexes on the same table, or online indexes that have the same identifier.

For indexes defined with the IN TABLE keyword option, however, the indexed table remains locked for the duration of the CREATE INDEX ONLINE operation. While the in-table index is being created, attempted access by other sessions to the locked table would fail with these errors: 
107: ISAM error:  record is locked.
211: Cannot read system catalog (systables).
710: Table (table.tix) has been dropped, altered or renamed.