Working towards a better Index - How DB2 index affects performance ? DB2 PERFORMANCE TUNING - PART 2

In the last post db2-performance-tuning we saw how adding a column into an index can improve DB2 performance. To refresh a bit, DB2 index is nothing but ordered set of values with one or more pointers called RID(record Ids) that indicate where those values can be found on a table.
So if a table have 3 indexes X1,X2,X3 ,the optimizer will choose that index (say X3) which will have all the columns needed in the query. In this case, there is no need to access the table at all. This type of access is called INDEX ONLY ACCESS.  But,then again,if putting everything into index solves the problem, then life would have been simple.:).
Indexes aren't free. The SQL insert, delete and update cause maintenance to the index when an indexed column is modified. So,While Determining the Access paths involving indexes, the optimizer comes up with these choices:
1. Will Index be used to satisfy the query ?
      a)  YES: Implies an index scan is selected
      b)  NO :  Implies only table scan is selected.
YES does not necessarily mean DB2 will scan or read index only. It means Index will be involved somehow in fetching the query result
2. Is INDEX SCAN sufficient for the query ?
     a) YES : means, only index scan is selcted. No involvement of the DB2 table
     b)  NO:  means both the index and table will be used.
With this comes the concept of matching column (MATCHCOLS=X) in the PLAN Table.In short,matching columns are nothing but all of those columns in the where predicate of the SQL query which are present in the Index being selected by optimizer.
So,how to find out Matching columns ? There are some basic rules which are used by optimizer to figure out the matching columns.
RULE 1
We can look at the index from left to right and compare the predicates in our query like below:
Example 1: Suppose we have defined INDEX on STATE,CITY in EMP table.(Point to Note  !!)

Written in blue are the SQL queries for which the matching columns will be examined.

Select * from EMP where STATE='AR' and CITY='AUR'
Here the MC will be 2 because the predicates STATE and CITY are present in the index and ordering from left to right.
Example 2: Select * from EMP where STATE='AR' and PIN=200
Here MC=1 
Example 3: Select * from EMP where CITY='AUR' and PIN=200
Here MC=0 

RULE 2
At most one IN-list predicate can be a matching predicate on an index. (ACCESSTYPE=N)
An IN-list index scan is a special type of matching index scan. At most one IN-list predicate can be a matching predicate on an index.
Example1: Select * from EMP where STATE='AR' and CITY  IN ('AUR',AUX')
Here MC = 2. As we see atmost one IN predicate is used.
Example2: Select * from EMP where STATE IN ('AR',CK') and CITY  IN ('AUR',AUX')
Here MC=1
As a generic rule, we want matching columns,ie MATCHCOLS to be on a higher side.
For a better design of index ,we evaluate placing the query predicate columns in the index, starting with the most unique column first, followed by the second most unique column and so on