Skip to content

ABAP Keyword Documentation →  ABAP - Reference →  Processing External Data →  ABAP - Database Accesses →  Databases - Overview 

Indexes in Database Tables

An index in a database table helps to speed up the selection of database records. An index is a sorted copy of selected database table fields.

Other versions: 7.31 | 7.40 | 7.54

Primary Index

The primary index is a unique index constructed from the key fields of the primary key. It is always created automatically in AS ABAP. A maximum of one record exists in the table for each combination of index fields. If the primary index cannot be used to identify the results set, for example because no field from the primary index was selected, then the whole table is scanned.

Secondary Indexes

To restrict the number of data records scanned when identifying the results set, unique and non-unique secondary indexes can be created in ABAP Dictionary. Creating secondary indexes is beneficial in the following cases:

  • If you want to select table entries based on fields that are not contained in an index, and the response times are very slow, then create a suitable secondary index.
  • The field or fields of a secondary index are so selective that each index entry corresponds to a maximum of 5% of the total number of table entries.
  • The database table is accessed mainly to read entries. When accessing a table to modify entries, each additional index must also be updated.
  • If only those fields are read that also exist in the index, then the data does not need to be accessed a second time after the index access. If only a very small number of fields are selected, there can be significant efficiency gains if these fields are included in an index in their entirety.

The SQL Trace tool (transaction ST05) can be used to determine which index is being used by the database system to access data. If an index is constructed from multiple fields, it can also be used if only a few of these fields are specified in a selection condition. However the sequence in which the fields are specified in the index is important. Generally, you can only use a field in the index if all of the preceding fields in the index definition are included in the selection condition (WHERE condition). In addition, an index can only support conditions which describe the search value positively, such as = or LIKE. The response times of conditions including <>, for example, are not improved by an index.

An index should only consist of a few fields; as a rule, no more than four. This is because the index has to be updated each time you change its fields in a database operation. Felder that are suitable for indexes are:

  • Fields that are selected often and that have a high level of selectivity. You should place the most selective fields at the beginning of the index.
  • You should not include a field in an index if its value is initial for most of the table entries.
  • If you are using more than one index for a database table, ensure that they do not overlap.

You should not create more than five indexes for any one table because

  • Each index produces additional update costs.
  • The amount of data increases.
  • The optimizer of the database system is given too many selection options and becomes more error-prone.

The optimizer generally stops if the selectiom condition contains an OR. In other words, it does not evaluate the fields checked by OR when selecting and applying the index. An exception to this are OR relationships standing on their own. Therefore, try to reformulate conditions containing an OR relationship for one of the indexed fields.


Note

The zero value in some database systems is ignored by the indexes, meaning that no index can be used when selecting by zero values.


Example

The optimizer stops working when it encounters OR in the following SELECT statement.

SELECT * FROM spfli
         WHERE carrid = 'LH' AND
              ( CITYFROM = 'FRANKFURT' OR  cityfrom = 'NEW YORK' ).

When replaced by the equivalent statement (below), the entire condition can be optimized with respect to the existing indexes.

SELECT *
       FROM spfli
       WHERE ( carrid = 'LH' AND cityfrom = 'FRANKFURT' ) OR
             ( carrid = 'LH' AND cityfrom = 'NEW YORK' ).