INDEX in Oracle SQL – PLSQL

In Oracle SQL /PLSQL an INDEX is basically a performance tuning method which allows us to retrieve or fetch the desired records faster.
An INDEX will create an entry for each value that is stored in the indexed columns.

Syntax for creating an INDEX in Oracle SQL / PLSQL is:

CREATE [UNIQUE] INDEX index_name
ON TABLE table_name (column_name1,column_name2, . . . ,column_nameN)
[COMPUTE STATISTICS];

• The keyword “UNIQUE” indicates that value / combination of values in the indexed column should be unique.
• COMPUTE STATISTICS keyword tells Oracle to collect the statistics when the index is being created, these statistics are then used by the Oracle Optimizer to choose the best plan for the execution of any SQL statement.

Example:

CREATE INDEX employee_index
ON TABLE employee (employee_id);

Here we have created an index named ‘employee_index’ on the ‘employee’ table it contains only one column ‘employee_id’.

We can create indexes with more than one columns as shown below:

CREATE INDEX employee_index
ON TABLE employee (employee_id, employee_name);

Here we have created an index named ‘employee_index’ on the ‘employee’ table it contains two columns ‘employee_id’ and ‘employee_name’.

Creating Function Based Index:

In Oracle we can not only create indexes based on columns but we can also create indexes based on functions.

Syntax for creating a FUNCTION BASED INDEX in Oracle SQL / PLSQL is:

CREATE [UNIQUE] INDEX index_name
ON TABLE table_name (function_name1,function_name2, . . . . function_nameN)
[COMPUTE STATISTICS];

Example:

CREATE INDEX emp_name_index
ON TABLE employee (UPPER(employee_name));

Here we have created an index named ‘emp_name_index’ based on the uppercase evaluation of the ‘employee_name’ column in the ‘employee’ table’.

Renaming an INDEX:

The syntax to RENAME an INDEX in Oracle SQL / PLSQL is:

ALTER INDEX index_name
RENAME TO new_index_name;

Example:

ALTER INDEX employee_index
RENAME TO emp_index;

Above statement will rename the ’employee_index’ to ’emp_index’.

Rebuilding an INDEX to compute statistics:

If we forget to COMPUTE STATISTICS while creating an INDEX we can REBUILD the index again to compute the statistics:

Syntax to REBUILD the INDEX in Oracle SQL / PLSQL is:

ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;

Example:

ALTER INDEX employee_index
REBUILD COMPUTE STATISTICS;

In the above statement we are collecting the statistics for the ‘employee_index’.

Dropping an INDEX:

Syntax for dropping an INDEX in Oracle SQL / PLSQL is:

DROP INDEX index_name;

Example:

DROP INDEX employee_index;