DB2 SQL- Rules for Creating Index

SQL and PLSQLIndexes will speed-up the performance. Before creating Indexs, the following rules can be considered.

  • 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.


Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.