James's Blog: Oracle: Choosing Index Keys

Monday, November 21, 2005

Guidelines for choosing keys to index:

Consider indexing keys that are used frequently in WHERE clauses.
Frequently to join tables in SQL statements.
have high selectivity(percentage of rows in a table that have the same value for indexed key). Indexing low selectivity columns can be helpful if the data distribution is skewed so that one or two values occur much less often than other values.
Do not use b-tree indexes n keys with few distinct vlues.
Do not index columns that are modified frequently
Do not index keys that appear only in WHERE clauses with functions or operators except with function based indexes.
Consider indexing foreign keys of referential intergrity constraints in cases in which a large number of I/U/D access the parent and child tables. this will avoid "share locking" on child table.
when choosing to index a key, you should balance the perf gain for queries and the perf loss for I/U/Ds by using SQL trace facility.