Access Database Design and Programming, Second Edition

Index Files

When a table is stored on disk, it is often referred to as a file. In this case, each row of the table is referred to as a record and each column is referred to as a field. (These terms are often used for any table.)

Since disk access is typically slow, an important goal is to reduce the amount of disk accesses necessary to retrieve the desired data from a file. Sequential searching of the data, record-by-record, to find the desired information may require a large number of disk accesses, and is very inefficient.

The purpose of an index file is to provide direct (also called random) access to data in a database file.

Figure 3.8 illustrates the concept of an index file. We have changed the Publishers data for illustration purposes, to include a city column. The file on the left is the index file and indexes the Publishers data file by the City field, which is therefore called the indexed field. The city file is called an index for the PUBLISHERS table. (The index file is not a table in the same sense as the PUBLISHERS table is a table. That is to say, we cannot directly access the index file—instead we use it indirectly.) The index file contains the cities for each publisher, along with a pointer to the corresponding data record in the Publishers file.

Figure 3-8. Index file between City and Publisher

An index file can be used in a variety of ways. For instance, to find ...