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.
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
Example 3: Select * from EMP where CITY='AUR' and PIN=200
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')
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