3.4.4 Access Path

Mandatory.

Specify the method to be used to maintain the IBM i database access path associated with this index.

When an index is created to arrange the information in a table into a specific order, the IBM i database creates an access path.

The access path is essentially the index "key columns" arranged in a special structure that allows:

• Extremely fast access to any individual record in the table using just the key(s) of the index.

• The processing of the records in order of the index key(s) without the need to first sort the table.

An access path exists for every index created. Every time a record in the table is added, deleted or updated the operating system must update all the access paths to this table. To do this it must use up part of the computer's time and available processing cycles. This of course degrades the performance of the system. The more access paths that are being maintained, the slower the system will run.

Default=Immediate.

Rules

Allowable values are:

IMMED	The access path should be maintained immediately (i.e. whenever a record is added, updated or deleted from the table). This option is the most common. If the index is to be used in an interactive environment then IMMED should always be used.
DELAY	The access path should only be maintained when the index is used. This type of access path lies dormant and is not maintained by the operating system until such time as someone needs to use the index. Thus it places no burden on the operating system until it is actually required. Typically, indexes views that use this option are only used very infrequently to "sort" a table into a specific order.

Platform Considerations

• IBM i: This table attribute applies to IBM i databases only.

Tips & Techniques

• If the index is to be used in an interactive environment, then this option should be selected.