As you probably already know DB2 are capable of avoiding access to the table itself if all columns belonging to one table in the SQL statement are present in the same index. This phenomenon is called an Index Only access. What you may not know or do not think about is the feature called index screening which I have described in week 3/2003. Index-screening may be used to tune the where clause of a SQL statement.
Imagine you have a table T containing columns A, B, C, D, E, F and G. There is an index defined on the table consisting of columns D, A and G in this order. You have just discovered the following SQL statement being performed on the table:
This SQL statement looks fine when you look at the result of an EXPLAIN. Two columns are matching so it looks like there is no room for further improvement. But there is. If the index on D, A and G may be extended with column B then DB2 does not need to to access data in order to find the value of column B. This may save a great deal. Actually the extension of the index will make the WHERE clause Index Only, although this situation is not reported in the EXPLAIN result.
You should be aware that an extension of an index with one or more columns may increase the number of levels in the index and then you will not acheive anything, because the saved access to data are now used for an extra access to the index and other SQL statements using the same index but not accessing the extra column will be punished. Also, it will not make much sense to extent an index with an extra column if this column contains the same value for most of the rows and this value is the one used most frequently.
In version 10 of DB2 it is possible to add extra columns to an index being unique without losing the original uniqueness. An index may consist of columns A, B and C, but be unique only on A and B. I have not yet any access to DB2 10, but I am pretty sure the number of levels may be affected if many new columns or a long single one are added to such an index. Please keep an eye on this if you combine this tip with the new feature for unique indexes in DB2 10.