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

• Best overall performance
• RAID system consists of 5 disks of data and parity
• Effective space utilization 80%
INDEXING TECHNIQUES
Take the example of an employee file consisting of data records where each data
record refers to one employee. If this is a file for a large organization, the employee
file may contain thousands of records stored on a huge number of storage blocks.
Depending on the file organization, to search for the record of a particular employee
you may have to search through several blocks. You have to search through a
large file with perhaps huge records. How can you make your search more
efficient?
Imagine another file that contains index records with just two fields—one having
the key values of the employee records and the other storing addresses of the data
records. Each index record points to the corresponding data record. So when you
want to search for an employee record with a particular key value, you do not go
directly to the employee file to search for the record. First, you go to the other
smaller file, search for the record with the desired key value, find the address of the
record in the employee file, and then fetch the record from the employee file. This
is the principle of indexing. The smaller file containing the key values and data
addresses is the index file.
Figure 12-11 illustrates the principle of indexing. Note the data file and the index
file.
The figure shows two types of indexes.
Dense Index. There is an index record for each data record.
Sparse Index. There is an index record for each block, not for each data record.
In Figure 12-11, the index file contains values of the primary key in the employee
file. An index file may be created based on any attribute in the data file. Indexes
created on the primary key are known as primary indexes. Indexes created on any
other field are called secondary indexes. Consider a few of the fields in the employee
record: EmployeeNo, JobCode, DeptNo, SkillType.
To speed up data access based on EmployeeNo, a primary index created on the
primary key field of EmployeeNo may be used. Now suppose you have data access
requests based on SkillType. If you want to retrieve all employees whose SkillType
is “Programming,” you have to retrieve each employee record and gather all the
records with this SkillType. Instead of this method, if you have an index file created
based on SkillType, then you can speed up your data access.
Figure 12-12 indicates the usage of primary and secondary indexes for data access
to the employee file. Note how, in the case of the primary index, one index value
points to one target data record. In secondary index files, one index value may point
to several target data records. For example, there could be several employees with
the SkillType “Programming.”