Index in Oracle

The index is a widely discussed subject among the Oracle fraternity. Most prolific use of an index in oracle is the faster retrieval of data. Indexes are filtered by a conditional logic from the table. They are logically and physically independent of data in the table. Without affecting any data, indexes can be created or dropped.

Create Index

Syntax,

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

Here,

Unique : The combination of columns indexed should be unique
index_name : A name given to an index
table_name : A name given to table
Column1, column2...column n : Columns of table

The clause in square brackets are optional.

Compute Statistics

Statistics are collected and stored into data dictionary using Compute Statistics option. This option helps the optimizer to choose execution plan during execution of SQL statement that accesses this table.

Example,

CREATE INDEX emp_index0 ON employees (emp_id);

Alter Index

There are various reasons for which we need to alter indexes. Some of it are:

1) Rebuilding indexes in parallel or reverse.
2) Rename an index
3) To compute statistics
4) Making indexes unusable
5) Renaming, splitting or dropping an index partition

Syntax,

ALTER INDEX <index_name> RENAME TO <new_index_name>;
ALTER INDEX <index_name> REBUILD COMPUTE STATISTICS;
ALTER INDEX <index_name> MONITORING USAGE;

Example,

ALTER INDEX emp_index0 RENAME TO emp_index1;
ALTER INDEX emp_index1 REBUILD COMPUTE STATISTICS; 
ALTER INDEX emp_index1 MONITORING USAGE;

Drop Index

Indexes are independent objects without any direct link or attachment to data in the table. Therefore, an index can be dropped anytime without affecting it.

Syntax,

DROP INDEX <index_name>;

Example,

DROP INDEX emp_index1;

Composite Index

An index created on the group of columns is referred to as a composite index. You can take any set of columns to create composite index irrespective of the order in which they are defined.

Example,

CREATE INDEX emp_index2 ON employees (emp_id, emp_number);

Function-based index

Index created on a function of a column used widely in WHERE clause is referred to as a Function-based index.

Example,

CREATE INDEX emp_index3 ON employees (UPPER(emp_name));

Some facts about index in oracle

  • By default, Oracle creates B-tree indexes
  • NULL values are not stored in indexes
  • You can create unlimited indexes on a single table. Unlimited here means limit up to the maximum possible combinations of columns if used differently
    Example,

    CREATE INDEX emp_index1 ON employees (emp_name, dept_id);
    CREATE INDEX emp_index2 ON employees (dept_id, emp_name);
  • Invisible index is the feature added in version 11g and later. It tells an optimizer to not consider the indexes while optimizing.
  • The maximum number of columns you can take in a single composite index is 32.

If you liked the above post, please leave your comments below.

Subscribe here to stay updated on latest posts of OracleMine.com.

Hi I am Paras.

Thanks for stopping by at OracleMine.com. Speaking about my brief introduction, I work for a multinational organisation in Oracle related technologies. Being an avid blogger, I would like to inform you about my productivity and motivational blog XpressPlanet.com. Speaking of OracleMine.com, I will try my best to share knowledge on technologies in as simple and understandable manner as possible. You can also contribute your knowledge on OracleMine by writing to us at hioraclemine@gmail.com. Again I appreciate your visit. Hope to see you again and again!

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.