Nonmatching index scan (ACCESSTYPE='I' and MATCHCOLS=0)

In a nonmatching index scan no matching columns are in the index. Consequently, all of the index keys must be examined.

Because a nonmatching index usually provides no filtering, only a few cases provide an efficient access path. The following situations are examples:

When index screening predicates exist

In that case, not all of the data pages are accessed.

When the clause OPTIMIZE FOR n ROWS is used

That clause can sometimes favor a nonmatching index, especially if the index gives the ordering of the ORDER BY clause or GROUP BY clause.

When more than one table exists in a nonsegmented table space

In that case, a table space scan reads irrelevant rows. By accessing the rows through the nonmatching index, fewer rows are read.