SELECT * FROM EMP;
A matching index scan begins at the root page of an index and works down to a leaf page in much the same manner as a direct index lookup does. However, because the complete key of the index is unavailable, DB2 must scan the leaf pages using the values that it does have, until all matching values have been retrieved. Consider a re-write of the previous query, this time without the EMPCODE predicate:
A matching index scan can locate the first leaf page with the appropriate value for DEPTNO and TYPE by traversing the index starting at the root. But there can be multiple index entries with this combination of values and different EMPCODE values. Therefore, leaf pages to the right are sequentially scanned until no more valid DEPTNO, TYPE, and varying EMPCODE combinations are encountered.
For a matching index scan to be requested, you must specify the high order column in the index key, which is DEPTNO in the preceding example. This provides a starting point for DB2 to traverse the index structure from the root page to the appropriate leaf page. But what would happen if you did not specify this high order column? Suppose that you alter the sample query such that a predicate for DEPTNO is not specified: