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 DBA can reduce the risk of non-exclusive 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. This instructs the database server to create the online index while concurrent users can continue to access the table.
The database server builds the index, even if other users are performing Dirty Read and DML operations on the indexed table, until the new index has been created. Immediately after you issue the CREATE INDEX . . . ONLINE statement, the new index is not 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 create a functional index, a clustered index, a virtual index, or an R-tree index.
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 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.
Related statements: ALTER INDEX, CREATE OPCLASS, CREATE TABLE , DROP INDEX , RENAME INDEX, and SET Database Object Mode
For a discussion of the structure of indexes, see your IBM Informix Administrator's Reference.
For a discussion of the different types of indexes and information about performance issues with indexes, see your IBM Informix Performance Guide.
For a discussion of the GLS aspects of the CREATE INDEX statement, see the IBM Informix GLS User's Guide.
For information about operator classes, refer to the CREATE OPCLASS statement and IBM Informix User-Defined Routines and Data Types Developer's Guide.
For information about the indexes that DataBlade modules provide, refer to your DataBlade module documentation.