Indexes

An index is a way to access and order records in a file. Each index represents an order defined by key fields in an index table. A file can have up to 16 indexes.

Every file must have a primary index. Third-party database implementations rely on a primary index to store data. To enhance processing for third-party databases, Database Definition asks you to select a primary index for each file. When you add a new file to a database, the first index you add must meet the criteria for a primary index (no duplicates or conditions). After you have added subsequent indexes, any index that meets the criteria can be designated as the primary index. Ideally, the primary index provides a path for reaching all records in a file efficiently.

For the procedure to define an index, see Defining an Index.

Key Fields

Each index can have up to 13 key fields. Fields that occur more than once or are of the types Signed, Percent, or Currency cannot be key fields. The maximum size of all the key fields in an index is 224 bytes.

Each key in an index can be stored in ascending or descending order. Key fields are ascending by default; the first record in the index is the one with the smallest value. For example, if the Department key field is ascending, the first record accessed might be for department 1, while the last record accessed might be for department 10.

For the procedures to work with key fields, see Manipulating Key Fields in an Index.

Index Conditions

An index condition allows you to create a subset of the file; only records satisfying the condition are accessible when a conditional index is used.

For the procedure to define a conditional index, see Defining Conditions.

Example

The Employee file has the following fields: Company (Co#), Employee (Emp#), Department (Dept#), and so on. The system adds records to the file as users or programs create the records.

Database management systems typically add records either to the end of the file or fill in holes within the file. Holes are created when records are deleted. Because there are no holes in this file, the system adds the record (Company 1, Employee 3, Department 1) to the end of the file.

To access Company 1 records in Employee order, you need an index. Indexes provide order to the physical file without changing the physical order of the records in the file. To access the Employee file records in Company and Employee order, define an index (Empset1) with the key fields Company and Employee. This example shows how the Empset1 index and the Employee file look.