3.1 Goal of Chapter

An index for a table is a data organization that enables certain queries to access one or more records of that table fast. Proper tuning of indexes is therefore essential to high performance. Improper selection of indexes can lead to the following mishaps:

• Indexes that are maintained but never used

• Files that are scanned in order to return a single record

• Multitable joins that run on for hours because the wrong indexes are present (we have seen this)

This chapter gives you guidance for choosing, maintaining,and using indexes. (Figure 3.1 shows the place of indexes in the architecture of a typical database system.) The hints apply directly to relational systems. If you use a nonrelational system, a VSAM-based, hierarchical, network-based, or object-oriented database system, then you will have to translate a few of the examples in your head, but you will find that the principles remain relevant.

Figure 3.1: Place of indexes in the architecture of a typical database system. Indexes are provided by the storage manager. They organize the access to data in memory and, for clustering indexes, also organize the layout of data on disks. Indexes are tightly integrated with the concurrency control mechanisms. They are heavily used by the query processor during query optimization.

3.2 Types of Queries

The usefulness of an index depends on how queries use the index. For example, if there is an index on attribute A, but no query ever mentions A, then the index...