DB2 index access paths

What an index access path is

An index is a sorted b-tree of key values plus a RID (or RID list) that points at the row. An index access path means the optimizer chose that tree as the way to find qualifying rows instead of (or before) walking the table space. On PLAN_TABLE you typically see ACCESSTYPE of I, I1, N, or the multiple-index family (M, MX, MI, MU). ACCESSNAME names the index. MATCHCOLS tells how much of the key was used as a matching start/stop condition.

Index access is not automatically faster than a table space scan. If you need 80% of a large table, sequential prefetch of data pages can beat thousands of random RID lookups. The optimizer compares estimated cost. Your job is to understand the path it chose and whether your predicates and index design gave it a fair chance.

Matching index scan (MATCHCOLS > 0)

In a matching index scan, predicates on the leading index columns give Db2 a place to start (and usually stop) in the tree. Only a slice of leaf pages—and then only the corresponding data pages if needed—should be read. High filtering makes this the classic OLTP path: one equal on EMPNO against a unique index is a handful of getpages.

IBM's matching rules, simplified for beginners:

• Walk the index columns from leading to trailing
• A column can match if there is an indexable boolean-term predicate on that column
• If a column has no matching predicate, matching stops (later columns cannot match, even if you have predicates on them)
• If the matching predicate is a range ( >, >=, <, <=, BETWEEN, LIKE with a prefix, NOT NULL in documented cases), that column can still match, but no later column can
• Leading matching predicates are generally equal or IN; the last matching column may be equal, IN, a range, LIKE prefix, or NOT NULL

1
2
3
4
5
6
7
8
9
10
-- Index XEMP5 (WORKDEPT, LASTNAME, HIREDATE, EMPNO)
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE WORKDEPT = 'A00'
   AND LASTNAME = 'SMITH'
   AND HIREDATE > '2015-01-01'
   AND EMPNO > '000100';
 
-- Typical plan: ACCESSTYPE I, MATCHCOLS = 3
-- Equal, equal, then range on HIREDATE. EMPNO cannot match after the range.

MATCHCOLS = 3 on a four-column index is still a matching scan. The fourth predicate can still be used as index screening if EMPNO is in the index, just not as a matching start/stop key.

Star-join fact-table indexes are a documented exception: a missing key predicate does not always stop matching on later fact-table columns. Ordinary OLTP indexes follow the stop rules above.

Nonmatching index scan (MATCHCOLS = 0)

A nonmatching index scan means Db2 cannot position into the tree from a leading-key predicate. It starts at the first leaf and reads leaf pages in order, applying whatever predicates it can. PLAN_TABLE shows ACCESSTYPE I (or similar index type) and MATCHCOLS = 0. ACCESSNAME is still filled in.

When is this useful?

• You have a predicate on a later key column but not on the first
• The query is index-only: scanning compact leaves is cheaper than a tablespace scan
• You need rows in index order (sometimes avoiding a sort)

When is it dangerous? When MATCHCOLS is 0, INDEXONLY is N, and a large fraction of RIDs are followed to data pages in random order. That pattern is often slower than ACCESSTYPE R with sequential prefetch. Clustering ratio and DATAREPEATFACTORF statistics influence whether Db2 would rather list-prefetch those RIDs or abandon the index.

1
2
3
4
5
6
7
-- Index is (WORKDEPT, LASTNAME). No predicate on WORKDEPT.
SELECT EMPNO, LASTNAME
  FROM HR.EMPLOYEE
 WHERE LASTNAME = 'SMITH';
 
-- Possible nonmatching index scan: MATCHCOLS = 0.
-- LASTNAME can still screen index entries if it is a key column.

Index-only access

Index-only access means every column this step needs is in the index, so Db2 does not read table data pages. PLAN_TABLE INDEXONLY = Y. This is often the cheapest index path: leaf pages are dense, and you avoid data getpages and possible random I/O.

INCLUDE columns (non-key columns added to a unique index) and extra trailing key columns exist partly so queries can become index-only without changing uniqueness. SELECT EMPNO, LASTNAME from an index that already contains both columns can go index-only; add PHONENO to the SELECT list and INDEXONLY often flips to N unless PHONENO is in the index too.

For UPDATE and DELETE, index-only can still apply to finding the row; the data page must be updated when the table row changes. EXPLAIN's INDEXONLY flag describes whether the read portion needed the table.

Matching and nonmatching scans can both be index-only. A nonmatching index-only scan of a skinny unique index can be a reasonable way to compute COUNT(*) or to retrieve a few indexed columns for a report. A nonmatching index-only scan of a huge non-unique index with almost no filtering is still a lot of leaf getpages—just usually fewer than a full table scan.

Matching predicates

A matching predicate is an indexable boolean-term predicate that participates in MATCHCOLS. “Indexable” means the predicate can match; it does not mean Db2 will choose the index. “Boolean term” means the predicate must be true on its own for the row to qualify (ANDed at the top level). A predicate that appears only inside a large OR with a stage 2 expression often cannot match.

• COL = value, COL = host-variable, COL IN (list) — typical matching equals / IN
• COL BETWEEN, COL > value, COL LIKE 'ABC%' — can match as the last matching column (range)
• COL LIKE '%ABC' — not matching (leading wildcard); may still be stage 1 screening
• YEAR(COL) = 2010 — usually not matching; rewrite as a range on COL

Stage and indexability are covered in depth on the indexable versus stage 2 page. The access-path takeaway: if you want MATCHCOLS to include a column, write a sargable boolean-term predicate on that column, and do not hide the column inside a function unless IBM documents that function as indexable.

Index screening versus data screening

Index screening

After matching sets the leaf range, Db2 can apply more predicates to columns that exist in the index but were not matching columns. That is index screening. Those predicates discard index entries before data-page I/O. On a composite index (WORKDEPT, LASTNAME, SALARY), matching on WORKDEPT and screening on LASTNAME avoids fetching data rows for the wrong last name.

Data screening

Data screening happens after the data row is read (or after index-only has already returned the index columns). Stage 1 data-manager predicates on table columns, then stage 2 RDS predicates (for example many expressions, some LIKE patterns, non-indexable ORs) filter the row. Data screening is later and therefore more expensive if it rejects a large fraction of rows that already paid for a getpage.

Design indexes so highly selective predicates can match or at least screen on the index. A residual stage 2 predicate that throws away 99% of rows after a tablespace scan is a common performance bug: the predicate looked like a WHERE clause, but it never helped the access path.

Sparse index

A sparse index in Db2 for z/OS is not a CREATE INDEX option you sprinkle on a table. It is an in-memory index the runtime can build on a work file—often a hash-style structure when the result fits in memory—so a nested loop join can probe an inner table that has no useful catalog index. If the sparse index does not fit, it can overflow to work files.

You will see sparse-index choices in EXPLAIN when the optimizer decides that building a small runtime index is cheaper than scanning the inner table for every outer row, or cheaper than a merge join with sorts. Db2 12 improved memory allocation across multiple sparse indexes in one query and reduced key duplication. Sparse indexes also appear on legs of UNION ALL and some outer joins when costing says they win.

Sparse index is a join/runtime technique, not a substitute for a real index on a high-volume OLTP inner table. If the same nested loop runs millions of times a day, a persistent index with good matching predicates is still the usual design.

Multiple index access (related)

When two indexes each filter well, Db2 may build RID lists, AND or OR them (MI / MU), then list-prefetch data pages. That is still index access, but the first PLAN_TABLE row is often ACCESSTYPE M with PREFETCH L. Details of RID lists and list prefetch live on the tablespace scans and prefetch page. For this page, remember: matching on each participating index still follows MATCHCOLS rules on the MX rows.

Reading the plan

1
2
3
4
5
SELECT TNAME, ACCESSTYPE, MATCHCOLS, ACCESSNAME,
       INDEXONLY, PREFETCH, METHOD
  FROM PLAN_TABLE
 WHERE QUERYNO = 1001
 ORDER BY QBLOCKNO, PLANNO, MIXOPSEQ;

Access path stability (preview)

Index paths change when statistics, clustering, or available indexes change. A matching three-column scan can become a tablespace scan after a mass insert if RUNSTATS and REBIND see a huge filter factor. Access path stability is the practice of noticing those changes (APCOMPARE), reusing a known path (APREUSE), and keeping previous package copies (PLANMGMT) so you can switch back. The next page covers that machinery. From the index side: capture EXPLAIN for important statements, and treat MATCHCOLS / ACCESSNAME / INDEXONLY as part of your regression checklist after REBIND.

Explain It Like I'm Five

A matching index scan is looking up a friend in a phone book when you know the last name and the first letter of the first name: you open near “Smith, A” and stop before “Smith, T.” A nonmatching scan is flipping every page of the phone book because you only know the city, which is printed in tiny type on each line. Index-only means the phone book already lists the number, so you never walk to the house. Index screening is crossing out lines that are the wrong first name before you leave the book. Data screening is walking to the house and then noticing it is the wrong person. A sparse index is quickly scribbling a mini phone book on scratch paper for a small list you are about to look up many times.

Exercises

1. For an index on (A, B, C), write three predicates that should yield MATCHCOLS 1, 2, and 3. Then add a range on B and predict MATCHCOLS.
2. EXPLAIN a SELECT of only indexed columns versus the same SELECT plus one extra table column. Compare INDEXONLY.
3. Find a query with MATCHCOLS = 0. Decide whether it is index-only and whether a tablespace scan might be cheaper.
4. Take a stage 2 predicate (for example YEAR(HIREDATE) = 2010) and rewrite it so HIREDATE can match an index.
5. On a sandbox, EXPLAIN a nested loop where the inner table has no index on the join column. Look for sparse-index or work-file clues in EXPLAIN output or IBM docs for your version.