How to Create Index in Oracle SQL with Example

What is an Index?

Index is created on existing tables to retrieve data more quickly. This is a performance tuning method for faster retrieval of records from a table. By default oracle creates B-tree indexes when you create an index on a table.
If you want to create an index on a table you can use “create index” command as shown below.

Syntax for Creating an Index in Oracle:

CREATE [UNIQUE] INDEX index_name 
	ON table_name (column-1, column-2, column-3,... column_n)
	[ COMPUTE STATISTICS ];

UNIQUE keyword indicates that the combination of values in the indexed columns must be unique.

COMPUTE STATISTICS keyword tells Oracle to collect statistics during the creation of index. These statistics are further used by the optimizer to choose a ‘plan of execution’ when SQL statements are executed.

Example:

CREATE INDEX empid_idx ON emp (emp_id);

In the above example, we have created an index on the emp table called empid_idx. It consists of only one field i.e. emp_id column/field.

We can also create an index with more than one column/field as shown in following example:

CREATE INDEX empid_idx ON emp (emp_id, emp_name);

We can also choose to collect statistics upon creation of the index:

CREATE INDEX empid_idx ON emp (emp_id, emp_name) COMPUTE STATISTICS;

How to Create a Function Based Index?

In Oracle, you can also create a function based indexes on only columns. You can create function based indexes using following syntax.

CREATE [UNIQUE] INDEX index_name
  ON table_name (function-1, function-2, function-3,... function_n)
  [ COMPUTE STATISTICS ];

Example:

CREATE INDEX empname_idx ON emp (UPPER(emp_name));

Here we have created a function based index on the emp_name column with uppercase evaluation.

If you want to be sure that the Oracle optimizer uses this index when executing your SQL statements then use the following condition to ensure.

Add UPPER(mep_name) IS NOT NULL in WHERE clause as shown below:

SELECT emp_id, emp_name, UPPER(emp_name)
FROM emp
WHERE UPPER(emp_name) IS NOT NULL
ORDER BY UPPER(emp_name);

How to Rename an Index?

You can use following syntax for renaming an index:

ALTER INDEX your_index_name RENAME TO new_index_name;

Example:

ALTER INDEX empid_idx RENAME TO emp_id_idx;

Here we are renaming the index called empid_idx to emp_id_idx.

How to Collect Statistics on an Index using REBUILD?

If you want to collect statistics on the index then you can use ALTER INDEX command with REBUILD option to collect statistics.

You can collect statistics on an index using following syntax:

ALTER INDEX your_index_name REBUILD COMPUTE STATISTICS;

Example:

ALTER INDEX empid_idx REBUILD COMPUTE STATISTICS;

In the above example, we are collecting statistics for the index called empid_idx.

How to Drop an Index?

You can drop an index using following syntax:

DROP INDEX your_index_name;

Example:

DROP INDEX empid_idx;

In the above example, we are dropping an index called empid_idx.

I hope you understood the way to create index in Oracle SQL.