Database Reference
In-Depth Information
CreditLimit Index
RepNum Index
CreditLimit
$5,000
RecordNum
RepNum
RecordNum
4, 8
20
1, 6, 10
$7,500
1, 3, 9, 10
35
2, 4, 8, 9
128
$10,000
2, 5, 7
65
3, 5, 7
$15,000
6
FIGURE 4-12
Indexes for the Customer table on the CreditLimit and RepNum fields
By examining the CreditLimit index in Figure 4-12, you can see that each credit limit occurs in the
index along with the numbers of the records on which that credit limit occurs. Credit limit $7,500, for exam-
ple, occurs on records 1, 3, 9, and 10. Further, the credit limits appear in the index in numerical order. If the
DBMS uses this index to find those records on which the credit limit is $10,000, for example, it could scan
the credit limits in the index to find $10,000. After doing that, it would determine the corresponding record
numbers (2, 5, and 7) and then immediately go to those records in the Customer table, finding these custo-
mers more quickly than if it had to scan the entire Customer table one record at a time. Thus, indexes can
make the process of retrieving records fast and efficient.
NOTE
With relatively small tables, the increased efficiency associated with indexes will not be readily apparent. In practice, it is com-
mon to encounter tables with thousands, tens of thousands, or even hundreds of thousands of records. In such cases, the
increase in efficiency is dramatic. In fact, without indexes, many operations in such databases would simply not be practical—
they would take too long to complete.
The field or combination of fields on which the index is built is called the index key. In the index shown
in Figure 4-11, the index key is CustomerNum; in the indexes shown in Figure 4-12, the index keys are
CreditLimit and RepNum. The index key for an index can be any field or combination of fields in any table.
After creating an index, you can use it to facilitate data retrieval. In powerful mainframe relational sys-
tems, the decision concerning which index(es) to use (if any) during a particular type of retrieval is a func-
tion of the DBMS.
As you would expect, the use of any index is not purely advantageous or disadvantageous. An advantage
was already mentioned: An index makes certain types of retrieval more efficient. There are two disadvan-
tages. First, the index occupies space on disk. Using this space for an index, however, is technically unneces-
sary because any retrieval that can be made using the index can also be made without the index, although
less efficiently. The other disadvantage is that the DBMS must update the index whenever corresponding data
in the database is updated. Without the index, the DBMS would not need to make these updates. The main
question you must ask when considering whether to create a given index is this: Do the benefits derived dur-
ing retrieval outweigh the additional storage required and the extra processing involved in update operations?
The following guidelines should help you make this determination. You should create an index on a field (or
combination of fields) when one or more of the following conditions exist:
The field is the primary key of the table. (In some systems, the DBMS might create this index
automatically.)
The field is the foreign key in a relationship you have created.
You will frequently use the field as a sort field.
You will frequently need to locate a record based on a value in this field.
the
index does not need to be created at the same time as the database. Likewise, when it appears that an exist-
ing index is unnecessary, you can delete it.
The exact process for creating an index varies from one DBMS to another. A common SQL command to
create an index is as follows:
You can add and delete indexes as necessary. You can create an index after the database is built
—
CREATE INDEX CustomerName
ON Customer (CustomerName)
;
