DB2 UDB for z/OS: Design Guidelines for High Performance and Availability

60 DB2 UDB for z/OS: Application Design for High Performance and Availability

2.1 What is an index?

An index is an ordered set of pointers to rows of a base table or an auxiliary table contained in

4 KB pages. Conceptually, you can think of an index to the rows of a DB2 table like you think

of an index to the pages of a book. Each index is based on the values of data in one or more

columns of a table.

An index is an object that is separate from the data in the table. You define an index using the

CREATE INDEX statement. DB2 builds the index B-tree (balanced tree) structure and

maintains it automatically. See Figure 2-1.

The number of index levels increases as the number of index entries increases, but an index

always has at least two index levels.

Pages of indexes that point directly to the data in tables are called leaf pages (level 0).

Besides the pointer to data, leaf pages contain the key and record-ID (RID). If an index has

more than one leaf page at level 0, then it needs the next level up in the hierarchy, with at

least one nonleaf page that contains entries that point to the leaf pages underneath. If the

index has more than one nonleaf page, then the index has a level 1. Depending on the

number of entries, other levels will be present. The highest level of an index contains a single

page, which is called the

root page.

Figure 2-1 Sample three-level index structure

The number of index levels is important because an extra index level means an extra getpage

for each (matching) index access (unless index look-aside can be used). For more

information on index look-aside, see Appendix A.4, “Index look-aside” on page 426.

Page A highest key of page A

Page B highest key of page B

ROOT PAGE

NONLEAF PAGE A NONLEAF PAGE B

KEY record-ID

LEAF PAGE 1

LEVEL 1

LEVEL 2

LEVEL 3

LEAF PAGE X

Page 1 highest key of page 1

Page X highest key of page X

LEAF PAGE Z

Page Z highest key of page Z

KEY record-ID

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .

TABLE

row row

row

KEY record-ID

. . . . . . . . . . . . . . . .