Oracle Indexes

In this post you will learn how to create, rename and drop indexes in Oracle/PLSQL with syntax and examples.

What are indexes in Oracle?

In Oracle, Indexes is a performance tuning method to extract records from tables more quickly. Indexes creates a record for each value that appears in the indexed columns. By default, Oracle creates B-tree indexes.

Create INDEX

Syntax for creating an index in Oracle/PLSQL:

CREATE [UNIQUE] INDEX index_name
ON table_name column1, column2, ... column_n)
[ COMPUTE STATISTICS ]

Options and arguments

• UNIQUE – Indicates that the combination of values in indexed columns must be unique.
• index_name – The name of the index.
• table_name – Name of the table for which the index is created.
• column1, column2, … column_n – Columns for use in an index.
• COMPUTE STATISTICS – This is Oracle’s message for collecting statistical data during the creation of an index. The statistics are then used by the optimizer to select “execution plan” when SQL queries are executed.

Let’s consider an example of how to create an index in Oracle/PLSQL. For example:

CREATE INDEX supplier_idx
ON supplier (supplier_name);

In this example, we created an index of the supplier table called supplier_idx. It consists of only one field, supplier_name.

The index can also be created for several fields, as in the example below:

REATE INDEX supplier_idx
ON supplier (supplier_name, city);

When creating an index, you can do the following to collect statistical data:

CREATE INDEX supplier_idx
ON supplier (supplier_name, city)
COMPUTE STATISTICS;

Create Function-Based INDEX

In Oracle, you are not limited to creating indexes on columns only. You can create indexes based on functions.

Syntax for creating a function-based index in Oracle/PLSQL:

CREATE [UNIQUE] INDEX index_name
ON table_name (function1, function2, ... function_n)
[ COMPUTE STATISTICS ]

Options and arguments

• UNIQUE – Indicates that the combination of values in indexed columns must be unique.
• index_name – The name of the index.
• table_name – Name of the table for which the index is created.
• function1, function2, … function_n – Functions for use in an index.
• COMPUTE STATISTICS – This is an Oracle message for collecting statistical data during the creation of an index. The statistics are then used by the optimizer to select “execution plan” when SQL queries are executed.

Let’s consider an example of how to create a function-based index in Oracle/PLSQL. For example:

CREATE INDEX supplier_idx
ON supplier (UPPER(supplier_name));

In this example, we created an index based on the upper case calculation of the supplier_name field using the UPPER function.

However, to make sure that Oracle optimizer uses this index when executing your SQL sentences, make sure that UPPER (supplier_name) does not calculate NULL values.

To ensure this, add UPPER (supplier_name) IS NOT NULL to your WHERE as follows:

SELECT supplier_id, supplier_name, UPPER(supplier_name)
FROM supplier
WHERE UPPER(supplier_name) IS NOT NULL
ORDER BY UPPER(supplier_name);

Rename INDEX

Syntax to rename index to Oracle/PLSQL:

ALTER INDEX index_name
RENAME TO new_index_name;

• index_name is the name of the index that you want to rename.
• new_index_name – the new name to be assigned to the index.

Let’s consider an example of how to rename an index in Oracle/PLSQL. For example:

ALTER INDEX supplier_idx
RENAME TO supplier_index_name;

In this example, we renamed the index with the name supplier_idx to supplier_index_name.

Collecting statistics on INDEX

If you did not specify the collection of index statistics when you first created the index, or you want to update the statistics, you can do so later by using the ALTER INDEX command to collect statistics.

Syntax for collecting statistical data on the index in Oracle/PLSQL:

ALTER INDEX index_name
I'M GONNA NEED TO GET SOME STATISTICS;

• index_name – the name of an index, for collecting statistics.

Let’s consider an example of how to enable statistics collection by index in Oracle/PLSQL. For example:

ALTER INDEX supplier_idx
I'M GONNA NEED TO GET SOME STATISTICS;

In this example, we collect statistics for the supplier_idx index.

Drop INDEX

Syntax to remove index index in Oracle/PLSQL:

DROP INDEX index_name;

• index_name is the name of the index we want to delete.

Let’s consider an example of how to remove index in Oracle/PLSQL. For example:

DROP INDEX supplier_idx;

In this example, we have removed the index supplier_idx.

SQL tutorial: Indexes In Oracle Database