Get Example source ABAP code based on a different SAP table
ARTICLE
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.
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 table entries are to be selected based on fields that are not contained in an index, and the response times are very slow, a suitable secondary index should be created.
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 function in the Performance 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 order in which the fields are specified in the index is important. Generally, a field is used in the index only 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 its fields are updated in a database operation. Fields that are suitable for indexes are:
Fields that are selected often and that have a high level of selectivity. The most selective fields should be placed at the beginning of the index.
A field should not be included in an index if its value is initial for most of the table entries.
If more than one index is used for a database table, they should not overlap. No more than five indexes should be created 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' ).
