Index in Oracle

Index :- An index is an ordered list of contents of the column (or a group of column) of a table.

An index is similar to a table. It contains at-least two columns:
1. A column having sorted data on which an index is created, and
2. A column representing ROWID for each row in a table.

Types of Indexes:
1. Duplicate Indexes.
2. Unique Indexes.
3. Simple Indexes.
4. Composite Indexes.

Unique Index	Duplicate Index
1. An index that does not allow duplicate values for indexed column, is called a Unique Index.	1. An index that allows duplicate values for indexed column is called Duplicate Index.
2. A Unique Index created automatically for a table, if contains a primary key or Unique key.	2. A Duplicate Index is not created on primary key and Unique key because it contains Duplicate values.

Simple Indexes: An index created on single column of a table is called a Simple Index.
Composite Indexes: An index created on more than one column is called Composite Index.

==> Creating an Index

1. Creating a Simple Index:

Syntax:
CREATE [UNIQUE] INDEX indexname
ON tableName (columnName);

1. By Default Index are created as a Duplicate Indexes.
2. If unique option is provided while creating an Index it will be considered as Unique Index.

2. Creating a Composite Index:

Syntax:
CREATE [UNIQUE] INDEX indexname
ON tableName (columnName1,columnName2);

1. If more than one column is provided while creating an Index it will be considered as Composite Index.
2. If Indexes created on more than two column other column will be considered only when the previous all columns contain duplicate data.
In above syntax Second column will be considered only when the first column contains Duplicate data in such case, sorting is performed based on data of the second column.

3. Destroying an Index:

Syntax:
DROP INDEX indexName;

To understand concepts of index we using employee table and apply different types of index on them as shown below

Selecting employee Table