Using Indexes in Oracle

This article is written about how to create, rename and drop indexes in Oracle with syntax and examples.

What is an Index in Oracle?

An index is a performance-tuning technique of allowing quicker retrieval of records. An index creates an entry for each value that seems in the listed columns. By default, Oracle creates B-tree indexes.

Create an Index

Syntax

The syntax for growing an index in Oracle/PLSQL is:

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

UNIQUE It indicates that the aggregate of values in the indexed columns should be unique. index_name The title to assign to the index. table_name The name of the desk in which to create the index. column1, column2, … column_n The columns to use in the index. COMPUTE STATISTICS It tells Oracle to acquire data in the course of the introduction of the index. The data are then used by using the optimizer to pick out a “plan of execution” when SQL statements are executed.

Example

Let’s appear at an instance of how to create an index in Oracle/PLSQL.

For example:

CREATE INDEX supplier_idx
  ON supplier (supplier_name);

In this example, we’ve got created an index on the provider table known as supplier_idx. It consists of only one discipline – the supplier_name field.

We may want to additionally create an index with more than one discipline as in the instance below:

CREATE INDEX supplier_idx
  ON supplier (supplier_name, city);

We could also choose to acquire records upon creation of the index as follows:

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

Create a Function-Based Index

In Oracle, you are not confined to creating indexes on only columns. You can create function-based indexes.

Syntax

The syntax for creating a function-based index in Oracle/PLSQL is:

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

UNIQUE It indicates that the aggregate of values in the indexed columns have to be unique. index_name The title to assign to the index. table_name The name of the table in which to create the index. function1, function2, … function_n The functions to use in the index. COMPUTE STATISTICS It tells Oracle to acquire facts during the creation of the index. The facts are then used with the aid of the optimizer to pick a “plan of execution” when SQL statements are executed.

Example

Let’s look at 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’ve got created an index based totally on the uppercase comparison of the supplier_name field.

However, to be certain that the Oracle optimizer makes use of this index when executing your SQL statements, be sure that UPPER(supplier_name) does not consider to a NULL value. To make certain this, add UPPER(supplier_name) IS NOT NULL to your WHERE clause 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 an Index

Syntax

The syntax for renaming an index in Oracle/PLSQL is:

ALTER INDEX index_name
  RENAME TO new_index_name;

index_name The name of the index that you wish to rename. new_index_name The new identify to assign to the index.

Example

Let’s seem at 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’re renaming the index known as supplier_idx to supplier_index_name.

Collect Statistics on an Index

If you forgot to accumulate facts on the index when you first created it or you want to replace the statistics, you can always use the ALTER INDEX command to gather data at a later date.

Syntax

The syntax for gathering records on an index in Oracle/PLSQL is:

ALTER INDEX index_name
  REBUILD COMPUTE STATISTICS;

index_name The index in which to accumulate statistics.

Example

Let’s seem to be at an example of how to accumulate statistics for an index in Oracle/PLSQL.

For example:

ALTER INDEX supplier_idx
  REBUILD COMPUTE STATISTICS;

In this example, we’re gathering statistics for the index known as supplier_idx.

Drop an Index

Syntax

The syntax for losing an index in Oracle/PLSQL is:

DROP INDEX index_name;

index_name

The name of the index to drop.

Example

Let’s seem to be at an instance of how to drop an index in Oracle/PLSQL.

For example:

DROP INDEX supplier_idx;

In this example, we’re shedding an index referred to as supplier_idx.