"Matching Indexes to SQL"

Course: BB20

presented by

Bonnie K. Baker

The purpose of this seminar is to help students design indexes that efficiently support your SQL and program logic. Using sample SQL and workshop labs, the key issues involved in index design will be discussed. These issues include:

• Column choices

• Key order

• DDL parameters

• Clustering

• Partitioning and non-partitioning index issues

• Partition independence

• DB2 RI support

• Maintenance overhead

• DASD consumption

• Index contention

• SQL performance

Special topics covered are NPI support for PI columns, ever-increasing keys in Type 2 indexes, unique where not null indexes, index screening issues, list prefetch issues, uniqueness detection, and misunderstandings about clusterration, leafdist, near- and farindref, and near- and faroffpos. In one session, the student will cover almost all of the issues involved in decisions about index creation, emphasizing those issues that are most likely to impact performance.