PHYSICAL DATABASE DESIGN - Fundamentals of Database Management Systems - page 215

253 368 477 641

To
Cyl
1

To
Cyl
2

To
Cyl
3

To
Cyl
4

To
Cyl
5

To
Cyl
11

140 192 253 307 330 368

To
Cyl
6

To
Cyl
7

To
Cyl
8

To
Cyl
9

To
Cyl
10

F I G U R E 8.17
The B

416 477 529 578 641

-tree index after the
cylinder 5 split

+

Remember the following about indexes:

An index can be built over any field of a file, whether or not the file is in physical
sequence based on that or any other field. The field need not have unique values.

■

An index can be built on a single field but it can also be built on a combination of
fields. For example, an index could be built on the combination of City and State
in the Salesperson file.

■

In addition to its direct access capability, an index can be used to retrieve the
records of a file in logical sequence based on the indexed field. For example, the
index in Figure 8.10 could be used to retrieve the records of the Salesperson file
in sequence by salesperson name. Since the index is in sequence by salesperson
name, a simple scan of the index from beginning to end lists the relative record
numbers of the salesperson records in order by salesperson name.

■

Many separate indexes into a file can exist simultaneously, each based on
a different field or combination of fields of the file. The indexes are quite
independent of each other.

■

When a new record is inserted into a file, an existing record is deleted, or an
indexed field is updated, all of the affected indexes must be updated.

■

Creating an Index with SQL Creating an index with SQL entails naming the index,
specifying the table being indexed, and specifying the column on which the index
is being created. So, for example, to create index A in Figure 8.21, which is an
index built on the Salesperson Number attribute of the SALESPERSON table, you
would write:

CREATE INDEX A ON SALESPERSON(SPNUM);

Hashed Files
There are many applications in which all file accesses must be done on a direct basis,
speed is of the essence, and there is no particular need for the file to be organized
in sequence by the values of any of its fields. An approach to file organization and
access that fills this bill is the hashed file. The basic ideas include:

The number of records in a file is estimated and enough space is reserved on a
disk to hold them.

■