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,

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,

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,

Example,

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,

Example,

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,

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,

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

Leave a Reply

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