Databases Reference
In-Depth Information
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 all publishers located
in Kansas City, Access can first search the alphabetical list of cities in the index file.
Since the list is alphabetical, Access knows that the Kansas City entries are all together,
and so once it reaches the first entry after Kansas City, it can stop fcthe search. In other
words, Access does not need to search the entire index file. (In addition, there are very
efficient search algorithms for ordered tables.) Once the Kansas City entries are found in
the index file, the pointers can be used to go directly to the Kansas City publishers in the
indexed file.
Also, since the index provides a sorted view of the data in the original table, it can be
used to efficiently retrieve a range of records. For instance, if the Books data were
indexed on price, we could efficiently retrieve all books in the price range between
$20.00 and $30.00.
A table can be indexed on more than one column; that is to say, a table can have more
than one index file. Also, a table can be indexed on a combination of two or more
columns. For instance, if the PUBLISHERS table also included a State column, we could
index the table on a combination of City and State, as shown in Figure 3-9.
Figure 3-9. Index file between City, State, and Publisher
An index on a primary key is referred to as a
primary index
. Note that Microsoft Access
automatically creates an index on a primary key. An index on any other column or
columns is called a
secondary index
. An index based on a key (not necessarily the
primary key) is called a
unique index
, since the indexed column contains unique values.
