Matching index scan (MATCHCOLS>0)

In a matching index scan, predicates are specified on either the leading or all of the index key columns. These predicates provide filtering; only specific index pages and data pages need to be accessed. If the degree of filtering is high, the matching index scan is efficient.

About this task

Begin program-specific programming interface information.

In the general case, the rules for determining the number of matching columns are simple, but with a few exceptions.

  • Look at the index columns from leading to trailing. For each index column, search for an indexable boolean term predicate on that column. (See Predicates and access path selection for a definition of boolean term.) If such a predicate is found, then it can be used as a matching predicate.

    Column MATCHCOLS in a plan table shows how many of the index columns are matched by predicates.

  • If no matching predicate is found for a column, the search for matching predicates stops.
  • If a matching predicate is a range predicate, then there can be no more matching columns. For example, in the matching index scan example that follows, the range predicate C2>1 prevents the search for additional matching columns.
  • For star joins, a missing key predicate does not cause termination of matching columns that are to be used on the fact table index.

The exceptional cases are:

  • For MX, or DX accesses and index access with list prefetch, IN-list predicates cannot be used as matching predicates.
  • Join predicates cannot qualify as matching predicates when doing a merge join (METHOD=2). For example, T1.C1=T2.C1 cannot be a matching predicate when doing a merge join, although any local predicates, such as C1='5' can be used.

    Join predicates can be used as matching predicates on the inner table of a nested loop join or hybrid join.

  • The XML index, containing composite key values, maps XML values to DOCID and NODEID pairs. The XML values (the first key value) in the composite keys can be specified in XPath expressions. By matching the XPath expression in an XMLEXISTS predicate to the XPath expression in a particular XML index, the index key entries which contain the matched key values can be identified. The DOCID and NODEID pairs of those identified index key entries can be used to locate the corresponding base table rows efficiently.

Matching index scan example

Assume an index was created on T(C1,C2,C3,C4): 

SELECT * FROM T
  WHERE C1=1 AND C2>1
    AND C3=1;

Two matching columns occur in this example. The first one comes from the predicate C1=1, and the second one comes from C2>1. The range predicate on C2 prevents C3 from becoming a matching column. End program-specific programming interface information.