What is INDEX ?

Index is created on existing table to retrieve row quickly.

For e.g. There are thousands of records in a table, retrieving information will take long time. Therefore Index has created on column which are accessed frequently. So that the information able to retrieve quickly. Index can be created on a single column or group of columns. When index is created, it first sort the data and then it assigns RowId for each row.

Syntax :
CREATE INDEX index_name
ON table_name (column_name1,column_name2...);
There are two type of index available in sql namely like
1.Clustered Indexes
2.Non clustered Index

Clustered Index :
            It can be created using "create index ..." syntax.
Non Clustered Indexes :
            They are created when a column is explicitly defined with PRIMARY KEY, UNIQUE KEY Constraint which is not based on sorted record but on a bookmark.

Note:
1) Even though sql indexes are created to access the rows in the table quickly, they slow down DML operations like INSERT, UPDATE, DELETE on the table, because the indexes and tables both are updated along when a DML operation is performed. So use indexes only on columns which are used to search the table frequently.
2) If we have less data, not required to create indexes.
3) In oracle database you can define up to sixteen (16) columns in an INDEX.