DB2 Creating Index: 6 Thumb Rules

Indexes speed-up the performance in database. Here are best practices to keep in mind while creating an index in DB2.

Best Practices to Create Index

Consider indexing on columns used in WHERE, GROUP BY, and ORDER BY clauses, as well as for UNION, INTERSECT, and EXCEPT statements.
Limit the indexing of very frequently updated columns.
If indexing an APPEND NO table without a hash, explicitly create a clustering index. Failure to do so will result in DB2 clustering data by the first index created. If indexes are subsequently dropped and recreated, this can change the clustering sequence if the indexes are created in a different order.
Consider clustering on columns in GROUP BY and ORDER BY specifications to improve sequential access. You might also consider clustering on columns used for joining, such as on the foreign key in a referential constraint. Doing so can improve join performance because most of the joined rows should be on the same pages.
If no sorting, grouping, or joining is required, analyze your WHERE clauses and cluster on the columns most frequently referenced
Choose the first column of multi-column indexes wisely, based on the following hierarchy. First, choose columns that will be specified most frequently in SQL WHERE clauses (unless cardinality is very low). Second, choose columns that will be referenced most often in ORDER BY and GROUP BY clauses (once again, unless cardinality is very low). Third, choose columns with the highest cardinality.

From DB2 V9 onwards, we can create Index on expression. By creating expression as alias, and we can create index on alias.