When to use Indexes

General notes on when to use Indexes

• Index keys that are used frequently in WHERE clauses
• Index keys that are used frequently to join tables in SQL statements
• Index keys that have a high selectivity. An Index's selectivity is optimal if few rows have the same value.
• Do not use B*-Tree indexes on keys with few distinct rows. Bitmap Indexes maybe used in this case. Unless a high concurrency OLTP application and updates on the columns frequent.
• Try not index columns that are modified frequently. UPDATE, INSERT, DELETE operations have to update the index as well as the table.
• If WHERE clauses use functions on columns, then create a function based index. Other wise the index for that column may not be used.
• Consider indexing foreign keys of referential integrity constraints. Such indexes allow UPDATES and DELETE's on the parent-child table without share locks on the child table. An index on the FK column will prevent full table scans of the child table.
• When creating Composite Indexes put the must frequently queried and/or most restrictive column first.
• Do not index columns when there are many nulls in the column and you do not search on the not null values.

******************************************
keywords: index
******************************************
rdbms version: 9i 10g 11g
******************************************