O'Reilly logo

DB2® Universal Database for OS/390® Version 7.1 Certification Guide by Susan Lawson, Richard Yevich

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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. These predicates ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required