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.

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. (For a definition of boolean term predicats, see Predicates and access path selection.) 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.

The number of index columns used for matching (MATCHCOLS=n)

If MATCHCOLS is 0, the access method is called a non-matching index scan and all the index keys and their RIDs are read. If MATCHCOLS is greater than 0, the access method is called a matching index scan and the query uses predicates that match the index columns.

In general, the matching predicates on the leading index columns are equal or IN predicates. The predicate that matches the final index column can be an equal, IN, NOT NULL, or range predicate (<, <=, >, >=, LIKE, or BETWEEN).

Matching predicates example

The following example illustrates matching predicates:

SELECT * FROM EMP
  WHERE JOBCODE = '5' AND SALARY > 60000 AND LOCATION = 'CA';
 
INDEX XEMP5 on (JOBCODE, LOCATION, SALARY, AGE);

The index XEMP5 is the chosen access path for this query, with MATCHCOLS = 3. Two equal predicates are on the first two columns and a range predicate is on the third column. Though the index has four columns in the index, only three of them can be considered matching columns. End program-specific programming interface information.