The ONLINE Keyword for Create Index

Most Database Management Systems including Oracle locks the table completely for reads and writes when performing Data Definition Language (DDL) operations on a table. This is because all DBMS has ACID properties. You will need to schedule a downtime for application to perform any DDL operations on table like adding/dropping index, constraints, partitions etc. if your system is constantly hitting databases.

The DDL fails if another user has already locked the table. You will need to wait till the lock is released before you can run a DDL on the table. You can reduce the risk of nonexclusive access error, and increase the availability of the Indexed table by including the ONLINE keyword with CREATE INDEX statement. ONLINE request dbms to create Index while minimizing the exclusive lock and Index is created while concurrent users are accessing the table where you are performing a maintenance on Index.

You can create,rebuild, and drop Indexes online. This enables you to update base tables at the same time you are building or rebuilding Indexes on that table. You can perform DML operations while the Index build is taking place, but other DDL operations are not allowed. Parallel execution is not supported when creating or rebuilding an Index online. Don’t mix PARALLEL and ONLINE in a single CREATE statement. I see this mixture all the time and Oracle rejects this combination. I cannot speak for other DBMS as I have not tested ONLINE feature on other databases. I would love to hear from SQLServer & MySQL geeks.

Keep in mind the time that it takes to build Index online is proportional to the size of the table and the number of concurrently executing DML statements. Therefore, it is best to start online Index builds when DML activity is low or no DML activities.

When do you use ONLINE? You can use while during CREATE, ALTER and DROP Index.

SYNTAX

CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
ALTER INDEX emp_name REBUILD ONLINE;
DROP INDEX emp_name ONLINE;

Specify ONLINE to indicate that DML operations on the table or partition will be allowed while dropping/creating/rebuilding an Index. Do you know any other DDL where you can apply ONLINE? The ONLINE keyword can be used on Informix, SQLServer, MySQL and some more DBMS.