Indexes

Oracle provides a number of data structures that are used to speed up the process of the retrieval of rows from the database tables. To understand an index better, take the example of a book having index in it. The index contains keywords or topics with page reference against them. If you want to find out a particular topic from a book, you do not have to shuffle through the pages. Rather you can check the topic and its page number from the index and then go directly to that page. The index in Oracle works in the same way as index in a book. Therefore, specifying index in the database table helps you retrieve data from the tables faster and easily.


Creating an Index
The syntax for creating an index is as follows:


CREATE [UNIQUE] INDEX index_name
ON table_name (index_column)
[COMPUTE STATISTICS];

In the above syntax, UNIQUE indicates that the combination of values in the indexed columns must be unique. COMPUTE STATISTICS instructs Oracle to collect statistical information while creating an index and index_name is the name of the index that you want to create.

Note that an index name should not be more than 30 characters. table_name specifies the name of the table on which you want to create an index and index_column specifies the name of the column on which an index is created for the table table_name.

For example:

CREATE INDEX Emp_index
ON Employee(Empno);

In this example, the index Emp_index is created on the Emp table. This index is created on a column, named Empid.

Creating a Composite Index
In Oracle, you can also create an index on more than one column of a table. An index that contains multiple columns for identifying the location of a row is called a composite index. The syntax for creating a composite index is as follows:

CREATE [UNIQUE] INDEX index_name
ON table_name (column1, column, ...);

In the above syntax, column1, column2, and so on specify the names of columns on which an index has to be created for the table table_name. You can also create an index with more than one field (column) as shown in the following example:

CREATE INDEX Emp_index
ON Employee(First_name, Description);

The statistical information that you can collect while creating an index is as follows:

CREATE INDEX Emp_index
ON Employee(First_name, Description)
COMPUTE STATISTICS;

Creating a Function-based Index
In Oracle, you are not restricted to create indexes only on the columns. You can also create an index based on functions. The syntax for creating an index based on functions is as follows:


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

For example:

CREATE INDEX Emp_index
ON Employee(UPPER(First_name));

In the above example, the index Emp_index is created on the basis of the upper-case evaluation of the Ename column of the Emp table.

As you know that Oracle uses an index when it executes the SQL statements. The reason behind the use of the index is to ensure that UPPER(Ename) does not evaluate to a NULL value. To do so, add UPPER(Ename) IS NOT NULL in the WHERE clause as follows:

SELECT Empno, First_name, UPPER(First_name)
FROM Employee
WHERE UPPER(First_name) IS NOT NULL
ORDER BY UPPER(First_name);

Renaming an Index
The syntax for renaming an index is as follows:

ALTER INDEX index_name
RENAME TO new_index_name;

In the above syntax, index_name is the name of the index that you want to change, and new_index_name is the new name of the index index_name.

For example:

ALTER INDEX Emp_index
RENAME TO Emp_index_name;

The above SQL statement will rename the index Emp_index to Emp_index_name.

Collecting the Statistical Information of an Index
If you forget to collect statistical information of an existing index or want to update it, you need to use the ALTER INDEX command.

The syntax for collecting the statistical information of an index is as follows:

ALTER INDEX index_name
REBUILD COMPUTE STATISTICS;


In the above syntax, REBUILD is a keyword, which indicates that Oracle will collect the statistical information of the given index.

For example:

ALTER INDEX Emp_index
REBUILD COMPUTE STATISTICS;

The above statement collects statistical information of the index named Emp_index.

Dropping an Index
You can drop only those indexes that are already in your schema. The syntax for dropping an index from the schema is as follows:

DROP INDEX Index_name;

In the above syntax, Index_name is the name of the index, which exists in the current schema.

For example:
The following statement will drop the Emp_index index from the current schema:

DROP INDEX Emp_index;

When an index is dropped from a schema, its definition is automatically removed from the data dictionary.

Viewing the Index Information
In Oracle, you can retrieve information about indexes using the USER_INDEXES data dictionary view. Indexes can be in-built or created by the user. Therefore, you can get information about indexes through the Views.