Creating and dropping an index in an online environment

You can use the CREATE INDEX ONLINE and DROP INDEX ONLINE statements to create and drop an index in an online environment, when the database and its associated tables are continuously available.

The CREATE INDEX ONLINE statement enables you to create an index without having an exclusive lock placed over the table during the duration of the index build. You can use the CREATE INDEX ONLINE statement even when reads or updates are occurring on the table. This means index creation can begin immediately.

When you create an index online, the database server logs the operation with a flag, so data recovery and restore operations can recreate the index.

When you create an index online, you can use the ONLIDX_MAXMEM configuration parameter to limit the amount of memory that is allocated to the preimage log pool and to the updator log pool in shared memory. You might want to do this if you plan to complete other operations on a table column while executing the CREATE INDEX ONLINE statement on the column. For more information about this parameter, see Limiting memory allocation while creating indexes online.

The DROP INDEX ONLINE statement enables you to drop indexes even when Dirty Read is the transaction isolation level.

The advantages of creating indexes using the CREATE INDEX ONLINE statement are:
  • If a new index is needed to improve the performance of queries on a table, you can immediately create the index without a lock placed over the table.
  • The database server can create an index while a table is being updated.
  • The table is available for the duration of the index build.
  • The query optimizer can establish better query plans, since the optimizer can update statistics in unlocked tables.
The advantages of dropping indexes using the DROP INDEX ONLINE statement are:
  • You can drop an inefficient index without disturbing ongoing queries that are using that index.
  • After the index is flagged, the query optimizer will not use the index for new SELECT operations on tables.

If you initiate a DROP INDEX ONLINE statement for a table that is being updated, the operation does not occur until after the table update is completed. After you issue the DROP INDEX ONLINE statement, no one can reference the index, but concurrent operations can use the index until the operations terminate. The database server waits to drop the index until all users have finished accessing the index.

An example of creating an index in an online environment is:

CREATE INDEX idx_1 ON table1(col1) ONLINE

An example of dropping an index in an online environment is:

DROP INDEX idx_1 ONLINE

For more information about the CREATE INDEX ONLINE and DROP INDEX ONLINE statements, see the HCL OneDB™ Guide to SQL: Syntax.