Designing Indexes for Performance
The following sections show the different access paths provided by indexes. We should ensure that indexes are in place to provide the best access path possible for all critical queries.
Indexes for Efficient Access
DB2 uses the following index access paths:
Matching index scan (MATCHCOLS > 0)
Nonmatching index scan (ACCESSTYPE = I and MATCHCOLS = 0)
IN-list index scan (ACCESSTYPE = N)
Multiple index access (ACCESSTYPE is M, MX, MI, or MU)
One-fetch access (ACCESSTYPE = I1)
Index-only access (INDEXONLY = Y)
Equal unique index (MATCHCOLS = number of index columns)
Matching Index Scan (MATCHCOLS > 0)
In a matching index scan, predicates are specified on either the leading or all of the index key columns. ...