Choosing Physical Tuning Options › Non-SQL Tuning Options › Index Tuning Options › Nonsorted Indexes

Nonsorted Indexes

Nonsorted indexes are another way of linking all occurrences of an entity when the database is sparsely populated with occurrences of that entity. A nonsorted index requires less CPU and storage than a sorted index. A nonsorted index might, however, be less effective than an index sorted by db-key value. If multiple entity occurrences reside on a page, an index ordered by db-key will reduce the I/Os necessary to retrieve all occurrences.

Nonsorted orders

If the entity occurrences in the index are not to be sorted, you can specify the logical placement of new index entries by indicating one of the following orders:

• FIRST creates a LIFO (last in, first out) order. The new index entry is positioned at the beginning of the index.
• LAST creates a FIFO (first in, first out) order. The new index entry is positioned at the end of the index.
• NEXT creates a simple list. The new index entry is positioned immediately after the entry for the current (most recently accessed) entity occurrence. The NEXT order is recommended as a default.
• PRIOR creates a reverse list. The new index entry is positioned immediately before the entry for the current entity occurrence.