Here is a brief note that I had prepared some time back. Hope it will be useful:
Need for Indexes
An index placed on a table helps to retrieve data faster.
A well placed index makes the application run faster when accessing the data.
This is possible because the Database engine looks at the index instead of the column when a select statement is fired on that column.
The buffer can hold more number of rows since the db engine does not go through all the columns of the table.
If more than one column is to be selected, then concatenated indexes are used. Indexes are sorted and can be unique.
Indexing is used in case of a search within the database
The following are the rules for indexing:
� The columns mentioned in SQL statement predicates ( the 'where' or 'and' selections ) should be indexed
for example : SELECT a.age, a.name WHERE a.id='P991';
If column 'id' was indexed, the DB engine looks for id='P991' in the index which is ordered and quickly finds the location through the rowid in the corresponding table. Else search has to be made from start to 'P991' till the record is found.
� If a column has a large number of distinct values (not repeated) it is a candidate for indexing.
� If multiple columns are continually referenced together in SQL statement predicates, these columns should be put together in a composite index.
� The section under IN, NOT IN, EXISTS are not indexed.
� The columns in the join condition of two tables are indexed.