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 the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial