Create Index Online

Creating an index online in Oracle PL/SQL involves adding an index to a table while allowing concurrent DML (Data Manipulation Language) operations to be performed on the table. This is particularly useful in scenarios where the table is large, and you want to avoid locking the entire table during the index creation process, allowing other transactions to continue.

Syntax

Here is the syntax of how you can create an index online in Oracle PL/SQL:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

-- The ONLINE keyword is used to create the index online
CREATE INDEX index_name
ON table_name (column1, column2, ...)
ONLINE;

-- Example with additional options
CREATE INDEX index_name
ON table_name (column1, column2, ...)
TABLESPACE tablespace_name
ONLINE
PARALLEL 4; -- You can specify the degree of parallelism

Explanation:

CREATE INDEX: This is the SQL statement used to create an index.

index_name: Specify the name for the index you are creating.

ON table_name: Indicates the table on which the index is being created.

(column1, column2, …): Specifies the columns on which the index is based.

ONLINE: This keyword is crucial for creating the index online, allowing concurrent DML operations on the table.

TABLESPACE tablespace_name: Optionally, you can specify the tablespace where the index will be stored.

PARALLEL 4: This is an optional clause that specifies the degree of parallelism for the index creation, allowing the process to utilize multiple CPU resources.

Create Index Online example

  -- create Index Online
CREATE INDEX stu_on_idx 
  ON STUDENTS_LIST (FIRST_NAME, LAST_NAME) 
  ONLINE;

Output: index STU_ON_IDX created.

Check index

SELECT INDEX_NAME, INDEX_TYPE, TABLESPACE_NAME
FROM USER_INDEXES
WHERE table_name='STUDENTS_LIST';  

Output:

INDEX_NAME	INDEX_TYPE	TABLESPACE_NAME
STU_ON_IDX	NORMAL	SYSTEM

Creating an index online has several advantages, including minimizing downtime, allowing for better performance during the index creation process, and ensuring that other transactions can still access and modify the table concurrently.

Keep in mind that the ability to create an index online depends on the version of Oracle Database you are using. Starting from Oracle Database 12c, the majority of index types can be created online. Always refer to the Oracle documentation for the specific version you are working with for the most accurate and up-to-date information.