Optimized Key Operations for Tables of Types SORTED and HASHED TABLE

Accessing single records using READ

If you use the READ statement with an explicit key specification in the form WITH KEY k1 = v1 ... kn = vn, the key access is optimized in all cases in which this is possible, that is:

• with tables of the type SORTED TABLE, if any initial part of the table key or even the entire table key is covered, because of the key specification.

• with tables of the type HASHED TABLE, only if the entire table key is covered.

If there is a partial key specification that is sufficient for the above conditions, then the system initially uses this partial key to search for an entry. In the case of sorted tables, this is done using binary search, that is with logarithmic effort; in the case of hash tables using the internal hash algorithm, that is with constant effort. If the system finds an entry, it checks whether the remaining key conditions are also met. In particular, this means that over-specified key specifications are also optimized.

Examples

The following READ statements are all optimized by the system:

DATA: wa     TYPE sflight,
      SrtTab TYPE SORTED TABLE OF sflight
                  WITH NON-UNIQUE KEY carrid connid,
      HshTab TYPE HASHED TABLE OF sflight
                  WITH NON-UNIQUE KEY carrid connid.

READ TABLE SrtTab INTO wa WITH KEY carrid = 'LH'.

READ TABLE SrtTab INTO wa WITH KEY carrid = 'LH'
                                   connid = '0400'.

READ TABLE SrtTab INTO wa WITH KEY carrid = 'LH'
                                   seatsmax = 100.

READ TABLE SrtTab INTO wa WITH KEY seatsmax = 100
                                   carrid = 'LH'.

READ TABLE HshTab INTO wa WITH KEY carrid = 'LH'
                                   connid = '0400'.

READ TABLE HshTab INTO wa WITH KEY carrid = 'LH'
                                   connid = '0400'
                                   seatsmax = 100.

Conversely, the following READ statements cannot be optimized because the specified key does not offer any optimization possibilities. Therefore, these tables result in a Full Table Scan (linear search of all table entries):

READ TABLE SrtTab INTO wa WITH KEY connid = '0400'.

READ TABLE SrtTab INTO wa WITH KEY seatsmax = 100.

READ TABLE HshTab INTO wa WITH KEY carrid = 'LH'.

Partially Sequential Quantity Access Using the WHERE Condition

The partially sequential access of a table segment of the type SORTED TABLE using a WHERE condition (LOOP ... WHERE ..., DELETE ... WHERE ..., MODIFY ... WHERE ...) is optimized, if:

• all simple conditions are linked using AND

• no parentheses are used

• at least one simple condition is of the form k = v hat

• any initial part of or even the entire table key is covered by the conditions of the form k1 = v1 ... kn = vn.

Similarly to the READ optimization, the starting point for the loop is determined by a binary search with the simple conditions, which partly or even completely cover the table key. From the starting point onwards, the loop is processed only so long as these simple conditions are still met.

Examples

The following LOOP statements are all optimized by the system:

LOOP AT SrtTab INTO wa WHERE carrid = 'LH'.
  ...
ENDLOOP.

LOOP AT SrtTab INTO wa WHERE carrid = 'LH' AND
                             connid = '0400'.
  ...
ENDLOOP.

LOOP AT SrtTab INTO wa WHERE carrid   = 'LH' AND
                             seatsmax > 99   AND
                             seatsmax < 200.
  ...
ENDLOOP.

while the following LOOP statements cannot be optimized and therefore result in a full table fsScan:

LOOP AT SrtTab INTO wa WHERE connid = '0400'.
  ...
ENDLOOP.

LOOP AT SrtTab INTO wa WHERE carrid >= 'LH' AND
                             connid = '0400'.
  ...
ENDLOOP.

Notes

1. As is the case with all key operations, it is always sensible for performance reasons to specify the keys in the same order as in the definition of the table key. If this is not the case, the key specifications have to be standardized against the table key, which can cause additional runtime costs (for example in the case of dynamic key specifications).
   
2. A key specification may contain neither duplicate nor overlapping partial keys. Otherwise a syntax or runtime error will occur.