Rule

  1. Primary and Unique Key constraints are automatically indexed by the database when the constraint is enabled and cannot be indexed separately.
  2. Foreign Key columns should only be indexed where they improve performance.
  3. Additional non-unique indexes must only be created for specific performance reasons on the basis of demonstrated need.
  4. Redundant Indexes must be removed.
  5. Two indexes must not share the same leading edge, i.e. The same column(s) as the first part(s) of the index.
  6. Indexes must be named <table name>_<index type><seq> where <index type> is: ‘PK’, ‘AK’, ‘FK’ or ‘IK’ and <table name> is truncated to 25 characters.  Note: ‘PK’ refers to a Primary Key index,  ‘FK’ refers to a Foreign Key index, ‘AK’ refers to an Alternate Key index,  ‘IK’ refers to a non-unique index.
  7. Indexed columns must be specified in order of decreasing selectivity, i.e. the first column in the index should have the highest number of distinct values. Note: This is to enhance performance.
  8. Columns with only a few distinct values relative to the total number of records must not be indexed. Bitmap Indexes should be used only when there is little or no update activity and tables have low cardinality.  Note: A serial scan through the table is faster.

Guidelines

  1. A further consideration to the ordering of columns in indexes is that it may be possible to cover a number of  indexing requirements through only one index.
  2. The indexing of frequently updated columns should be carefully considered as the cost of maintaining the index may outweigh the benefits of the index.  Note: If a column is frequently updated then having an index on it will impact performance because the index will also need to be maintained and this may not be acceptable.