Special Features of Secondary Indexes
How well an existing index supports data selection from a table largely depends on whether the data selected with the index represents the data that has to ultimately be selected. This can best be shown using an example.
An index is defined on fields FIELD1, FIELD2, FIELD3 and FIELD4 of table BSPTAB in this order. This table is accessed with the SELECT statement:
SELECT * FROM BSPTAB WHERE FIELD1 = X1 AND FIELD2 = X2 AND FIELD4= X4
Since FIELD3 is not specified more exactly, only the index sorting up to FIELD2 is of any use. If the database system accesses the data using this index, it quickly finds all the records for which FIELD1 = X1 and FIELD2 = X2. You then have to select all the records for which FIELD4 = X4 from this set.
The SQL statement above is a pseudo code that explains the corresponding functionality. The syntax of this statement is not the same in ABAP.
The order of the fields in the index is important for the accessing speed. The first fields must be those which have constant values for a large number of selections. During selection, an index is only of use up to the first not specified field.
Only those fields that significantly restrict the set of results in a selection are rational for an index.
The following selection is frequently made on address file ADRTAB:
SELECT * FROM ADRTAB WHERE TITEL = 'Prof.' AND NAME = X AND VORNAME = Y
The field TITLE rarely restricts the records specified with NAME and FIRSTNAME in an index on NAME, FIRSTNAME and TITLE, since there are not many people with the same name and different titles. In this case the index is not rational. An index on field TITLE alone is sensible for example if all professors are frequently selected.
The SQL statement above is a pseudo code that explains the corresponding functionality. The syntax of this statement is not the same in ABAP.
Additional indexes can also place a load on the system since they must be adjusted each time the table contents change. Each additional index slows down the insertion of records in the table.
For this reason, tables in which entries are frequently written must have only a few indexes.
The database system sometimes does not use a suitable index for a selection, even if there is one. The index used depends on the optimizer used for the database system. You must check if the index you created is also used for the selection (see How to Check if an Index is Used ).
Creating an additional index can also have side effects on the performance. This happens if an index that was used successfully for selection is not used any longer by the optimizer, because the optimizer has estimated (sometimes incorrectly) that the newly created index is more selective.
The indexes on a table must be as disjunctive as possible. That is, they must contain as few fields in common as possible. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.