There is a mechanism in the DB2 optimizer called index-screening. Index-screening is a good idea that the optimizer doesn't tell about at all. The idea is to search an index for a given value even though DB2 has to scan all of the index or hopefully only a tiny part of it. In the PLAN_TABLE the optimizer tells us about the number of columns DB2 uses to get a direct hit on one or more rows, but if a column is the last column in a composite index of three columns and there is only a macthcols value of 1 then DB2 will use the third column as well, but it has to scan to match this third column. Let me show you an example to clarify this (hopefully):
Let us assume a table T with columns A, B, C, D, E, F and G. On this table there is an index consisting of the columns D, A and G in that order. Imagine that you execute this SQL statement:
If you run an EXPLAIN of this SQL statement it will probably tell you that DB2 will use the first column of the index (matchcols=1), but it dosn't tell you that it will use column G as well. DB2 has to scan all of the index entries matching D=5 to find those entries matching G=7 as well. Matchcols only tell us the number of columns DB2 uses to get a direct hit in the index without having to scan the index.
If you run an explain of the same SQL statement without the predicate D=5, then the PLAN_TABLE probably will show a full index-scan (matchcols=0). This is the only case where DB2 actually reveils its ability to use index-screening. And it can be a pretty good idea, if only a small part of the rows in table T hold the value 7 in column G and if the index is much smaller than the table it is based upon.
There is a column in the PLAN_TABLE called PREFETCH. If this column has the value L meaning list prefetch, then DB2 will disable index-screening. IBM is working on removing this limitation. Maby it has already been removed in version 7 and I am sure it will be removed in version 8, but there is no way to tell except by using a DB2 monitor.
Because of index-screening you should always consider to use columns in your predicates that are part of an index even though the column in question is not the first column in the index. This can be a very good idea for large tables. Sometimes using a column this way can save you from creating an extra index, even though a customized index would be faster. You can also extend indexes with extra columns in order to get index-screening, but there is a limit to this approach, because when the index gets too big, a tablespace scan can be much quicker.