A key is a column or expression on which you can build an index. Follow these
guidelines for choosing keys to index:
■ Consider indexing keys that are used frequently in WHERE clauses.
■ Consider indexing keys that are used frequently to join tables in SQL statements.
For more information on optimizing joins, see the section "Using Hash Clusters for
Performance" on page 15-11.
■ Choose index keys that have high selectivity. The selectivity of an index is the
percentage of rows in a table having the same value for the indexed key. An
index's selectivity is optimal if few rows have the same value.
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 standard B-tree indexes on keys or expressions with few distinct
values. Such keys or expressions usually have poor selectivity and therefore do not
optimize performance unless the frequently selected key values appear less
frequently than the other key values. You can use bitmap indexes effectively in
such cases, unless the index is modified frequently, as in a high concurrency OLTP
application.
■ Do not index columns that are modified frequently. UPDATE statements that
modify indexed columns and INSERT and DELETE statements that modify
indexed tables take longer than if there were no index. Such SQL statements must
modify data in indexes as well as data in tables. They also generate additional
undo and redo.
■ Do not index keys that appear only in WHERE clauses with functions or operators.
A WHERE clause that uses a function, other than MIN or MAX, or an operator with
an indexed key does not make available the access path that uses the index except
with function-based indexes.
■ Consider indexing foreign keys of referential integrity constraints in cases in
which a large number of concurrent INSERT, UPDATE, and DELETE statements
access the parent and child tables. Such an index allows UPDATEs and DELETEs on
the parent table without share locking the child table.
■ When choosing to index a key, consider whether the performance gain for queries
is worth the performance loss for INSERTs, UPDATEs, and DELETEs and the use of
the space required to store the index. You might want to experiment by comparing
the processing times of the SQL statements with and without indexes. You can
measure processing time with the SQL trace facility.[@more@]