Indexing (Cluster and non Cluster)

Indexing is similar to what we used day to day life while reading book. Our hand book has a index which help us to find the desired data in less amount of time. We use to search the string in the Index which specify us to go to page where if reside.

Same is the case with Indexing in Oracle. In Oracle data are stored in Table. When the data grows in huge quantity it becomes difficult for searching the data. For this purpose Oracle use the same Indexing concept.

There are two type of Indexing

1-      Cluster:- In cluster indexing data are arranged physically in the same fashion as it is indexed. Best example is primary key index. By doing Cluster index all the rows are sorted and arranged physically on Hard disk as per the sequence given by cluster index.

2-      Non-Cluster:- In non-cluster environment oracle maintain a list with values that store the pointer to the index values. Having said this we can have more than one non-cluster index for a single table. More over this non-cluster index is logical in nature means not stored physical on hard disk.

Now the concern is which one to us. Use this thum rule

-          If we want to perform select operation more go for Cluster index.

-          If we want to perform update, Insert etc operation go for Non-Cluster Index.