Designing Indexes for Performance

The following sections show the different access paths provided by indexes. We should ensure that indexes are in place to provide the best access path possible for all critical queries.

Indexes for Efficient Access

DB2 uses the following index access paths:

  • Matching index scan (MATCHCOLS > 0)

  • Index screening

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

  • IN-list index scan (ACCESSTYPE = N)

  • Multiple index access (ACCESSTYPE is M, MX, MI, or MU)

  • One-fetch access (ACCESSTYPE = I1)

  • Index-only access (INDEXONLY = Y)

  • Equal unique index (MATCHCOLS = number of index columns)

Matching Index Scan (MATCHCOLS > 0)

In a matching index scan, predicates are specified on either the leading or all of the index key columns. ...

Get DB2® Universal Database™ for OS/390™ v7.1 Application Certification Guide now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.