The purpose of secondary indexes

Secondary indexing provides a way to meet the different processing requirements of various applications. Secondary indexing allows you to have an index based on any field in the database, not just the key field in the root segment.

When you design your database records, you design them to meet the processing requirements of many applications. You decide what segments will be in a database record and what fields will be in a segment. You decide the order of segments in a database record and fields within a segment. You also decide which field in the root segment will be the key field, and whether the key field will be unique. All these decisions are based on what works best for all of the processing requirements of your applications. However, the choices you make might suit the processing requirements of some applications better than others.

Example: A database record in an educational database is shown in the following figure.
Figure 1. Database record in educational database
Database record with root COURSE. Below COURSE is CLASS segment. Below CLASS are segments INSTR and STUDENT.

The following figure shows the root segment, COURSE, and the fields it contains. The course number field is a unique key field.

Figure 2. Example of a database record unique key field
COURSE segment has the following fields: Class Date, Course Number (Key Field), Course Name, Class Room Number, Room Size, Total Attended.

You chose COURSE as the root and course number as a unique key field partly because most applications requested information based on course numbers. For these applications, access to the information needed from the database record is fast. For a few of your applications, however, the organization of the database record does not provide such fast access. One application, for example, would be to access the database by student name and then get a list of courses a student is taking. Given the order in which the database record is now organized, access to the courses a student is taking requires a sequential scan of the entire database. Each database record has to be checked for an occurrence of the STUDENT segment. When a database record for the specific student is found, then the COURSE segment has to be referenced to get the name of the course the student is taking. This type of access is relatively slow. In this situation, you can use a secondary index that has a set of pointer segments for each student to all COURSE segments for that student.

Another application would be to access COURSE segments by course name. In this situation, you can use a secondary index that allows access to the database in course name sequence (rather than by course number, which is the key field).