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


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.


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



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.



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.


Function-based index

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


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
  • 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

Hi I am Paras.

Thanks for stopping by at 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 Speaking of, 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 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.