Records in a table are indexed by the primary key of the table. For example, a customer record is indexed by the key CUSTOMER_NUMBER. As a rule, the customers are displayed in the order of the key field.
Records can also be indexed by tables higher in the table structure. For example, the CUSTOMER_NUMBER is included as a secondary key in the ORDER_HEADER table. This allows order records to be indexed in customer sequence.
If you want to index the records in a table by a key other than the primary key of the table, you define an index-only table that contains the key. Unlike tables that store records, index-only tables do not have to have a unique primary key.
For example, to view the records on the CUSTOMER table in order of the customer name, an index-only table can be used. This table defines NAME as the key field on the table:
INDEX_CUST_NAME
___________________________
1 NAME C30
The index-only table is a parent table of the CUSTOMER table, and allows the customer records to be viewed in name order.
The DBMS Database Manager has work to do to maintain secondary indexes, so you should switch off any you do not need. On the other hand, you can sometimes save a lot of processing with a secondary index. For instance, if your application needs to know which is the most recent order line in an order, and you average 50 order lines for each order, it may be helpful to have a secondary index on order lines with keys of order number and insertion date. In general, secondary indexes are worth having if you cannot write the application without them (an example would be an index to customers by name rather than region code) or if they substantially reduce the proportion of records that need to be accessed. An example of a secondary index that is unlikely to be worth having is one on a credit/debit indicator, which only reduces the proportion to be selected to a half.
Prior to release 4.622, DP4 allows you to create physical indexes in addition to the indexes that are built from keys. Physical indexes reference the physical position of records on the disk. Although a physical index may provide faster access than the prime index and, therefore, give better performance for enquiries and reports that are run on the database, it may slow down database updates. Physical indexing also slightly increases the size of the index file.
If a physical index is available for a table, the F_PHYS_INDEX flag, as defined in DBFLAGS.H, is set in the @REL_FLAGS field of the @RELDATA record. Using the DP4 C interface, for example, records can be fetched through a physical index like this:
fetch(FIRST,&datarec.1,PHYSREC_TYPE,0);
while (there)
{
/* process the record ... */
fetch(NEXT,&datarec.1,PHYSREC_TYPE,0);
} |
This code fetches all the table records in the random order in which they are physically arranged in the data file.
A physical index is unlikely to give significantly faster access than the prime index when the data file has been sorted by a database reorganisation. From release 4.622 REORGDB -DATA sorts data into prime index sequence, so there is no advantage to this type of index, and it is therefore no longer supported.