THE PHYSICAL DESIGN PROCESS - Database Design and Development: An Essential Guide for IT Professionals - page 368

Option1:

Option 2 :

JobCode
101
101
101
111
111
211
211
211
321
321
445
445

Address
19
67
89
33
58
26
45
98
37
76
53
54

JobCode
101
111
211
321
445

Address1
19
58
26
76
53

Address2
67
33
98
37
54

Address3
89

45

Option3 :

Address
of first
19
58
26
76
53

JobCode
101
111
211
321
445

Figure 12-16 Secondary indexes.

example, trace the search for the data record with key value 92654. Start the search
at the root node containing index value 51247. The search key value 92654 is greater
than 51247. Therefore, follow the right pointer from the root and go to the index
record with key value 82928. The search value 92654 is greater than 82938, so follow
the right pointer from this index record and go to the index record with key value
92654. This is the index record you are looking for. Pick up the data pointer from
this index record and go to the data record.
B-tree indexes come with a few important variations. Nevertheless, the indexing
principle is the same. Most DBMSs automatically create B-tree indexes for primary
keys. The DBA need not explicitly create primary indexes.

Secondary Indexes

Secondary indexes are built on any fields other than the primary key. A secondary
index may contain more than one field. Figure 12-16 indicates three options for cre-
ating index file records. The figure presents indexes built on the field JobCode in
the employee file.

Option 1: One index entry in secondary index file for each value-address pair.
The index file contains as many records as the data file. Retrieval of index
value 101 results in retrieval of 3 index records and then retrieval of corre-
sponding 3 data records.
Option 2: One index entry in secondary index file for each value with a list of
address entries. The index file contains a lower number of records than the
data file. Retrieval of index value 101 results in retrieval of just 1 index record
and then retrieval of corresponding 3 data records. If there are too many dis-
tinct data records for the same index value, this option will not work.
Option 3: One index entry in secondary index file for the first value-address pair,
with a pointer in the data record to the next target data record, and so on.