By default, DROP 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 dropped. The DROP 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 DROP INDEX statement. The ONLINE keyword instructs the database server to drop the index while minimizing the duration of an exclusive locks. The index can be dropped while concurrent users are accessing the table.
After you issue the DROP INDEX ... ONLINE statement, the query optimizer does not consider using the specified index in subsequent 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 dropped. Query operations that were initiated prior to the DROP INDEX ... ONLINE statement, however, can continue to access the index until the queries are completed.
When no other users are accessing the index, the database server drops the index, and the DROP INDEX . . . ONLINE statement terminates execution.
By default, the DROP INDEX ... ONLINE statement does not wait indefinitely for locks to be released. If one or more concurrent sessions hold locks on the table, the statement might fail with error -216 or -113 unless you first issue the SET LOCK MODE TO WAIT statement to specify an indefinite wait. Otherwise, DROP INDEX ... ONLINE uses the waiting period for locks that the DEADLOCK_TIMEOUT configuration parameter specifies, or that a previous SET LOCK MODE statement specified. To avoid locking errors, execute SET LOCK MODE TO WAIT (with no specified limit) before you attempt to drop an index online.
You cannot use the CREATE INDEX statement to declare a new index that has the same identifier until after the specified index has been dropped. No more than one CREATE INDEX ... ONLINE or DROP INDEX ... ONLINE statement can concurrently reference indexes on the same table.
The indexed table can be permanent or temporary, logged or unlogged, and fragmented or non-fragmented. You cannot specify the ONLINE keyword, however, when you drop a virtual index, a clustered index, or an R-tree index.
Related statements: ALTER TABLE, CREATE INDEX, CREATE TABLE . and CREATE Temporary TABLE . For the effect of indexes on performance, see your IBM Informix Performance Guide.
For more information on virtual indexes, see the IBM Informix Virtual-Index Interface Programmer's Guide.