INDEXES IN ORACLE

I N D E X E S

In Oracle indexes have two main purposes. They are

i.) To speed up the retrieval of rows via a particular key
ii.) To enforce uniqueness on values in a column.

The use of indexes is strongly recommended to achieve better performance. Once created, oracle will use the index whenever possible to speed up the data retrieval and this use is automatic and usually requires no action by the user who need never know of the existence of the index. Oracle will automatically create index on unique or primary key columns when the table was created.

Creating An Index :

Syntax : Create [Unique][Bitmap] Index idxname On Tabname(Col1, Col2, Col3,… , Col n);

Example : Create Index DNOIDX On EMP(Deptno);

This will create an index with name DNOIDX on Deptno column of EMP table.

Types Of Indexes :

Single Column Index : If an index is created on a single column of a table then that index is called as a Single Column Index.

Concatenated Index : If an index is created on more than one column of a table then that index is called as Concatenated Index.

Unique Index : Unique indexes are created by using unique keyword in the create index command. The purpose of unique index is to enforce uniqueness on the column even unique or primary constraint is not given on that column.

Non-Unique Index : An index created without unique key word is called as a non-unique index.
Bitmap Index : The bitmap index is created by using bitmap keyword in the create index command. The difference between normal and bitmap index is in bitmap indexes the identification of rows is in the form of binary digits i.e. 0, 1.

When Oracle Uses Index : Oracle will not use the index whenever user retrieves data from a table. Oracle uses index in the following situations.
When the indexed column is referenced in where clause.
Oracle will not use the index when the indexed column is used as a part of a function in where clause. (Ex : Upper(Ename))
When a concatenated index is created, all columns listed in the index must be used in the where clause to use index by Oracle.

We can create an Index on a column that allows function-based access. For this during the creation of index specify the column name along with function name.

Example: Create Index EnameIdx On EMP(UPPER(Ename));

Deleting An Index :

Syntax : Drop Index IndexName;

Example : Drop Index DnoIdx;