The ONLINE keyword of DROP INDEX

For indexes that were not defined with the IN TABLE keyword option, 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 DROP INDEX statement. The ONLINE keyword instructs the database server to drop the index while minimizing the duration of an exclusive lock, so that the index can be dropped while concurrent users are accessing the table.

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.

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 in a DROP 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 dropping an index that has any of the following attributes:
  • a functional index
  • a clustered index
  • a virtual index
  • an R-tree index.
The following statement instructs the database server to drop online an index called idx_01: 
DROP INDEX IF EXISTS idx_01 ONLINE;
For indexes defined with the IN TABLE keyword option, however, the indexed table remains locked for the duration of the DROP INDEX ONLINE operation. While the in-table index is being dropped, attempted access by other sessions to the locked table would fail with one or more of these errors: 
107: ISAM error:  record is locked.
211: Cannot read system catalog (systables).
710: Table (table.tix) has been dropped, altered or renamed.