An index contains a copy of just a few fields of a database table. In the index these fields are sorted -- in contrast to the data in the table itself. This makes it possible for the system to access the data in the index more quickly. Furthermore, each index entry "points" to the corresponding data record in the table.

When conditions are specified for fields contained in an index during a search request, the system can process part of the search request simply using the data contained in the index. As a result, certain index entries -- and their corresponding data records -- can be ruled out without the system having to read the table directly.

The system then needs to read the data records in the table which correspond to the remaining index entries. Only then can it be determined whether a data record also fulfills the rest of the search request, that is, whether it makes the hit list. The time needed to read complete data records is long, compared with that needed to read the index.

Consequently, one should try to define the index so that the number of data records that need to be read from the table is not much larger than the number of records that fulfill the search condition. This kind of index is referred to as a selective index, since it performs most of the selection for the search request.

If you are using the characteristics "Customer" and "Product" in the segment level, the system automatically transfers billing documents from SD to CO-PA with that level of detail. Thus when the system searches for the profitability segment number for a billing document item, values for both characteristics "Customer" (KNDNR) and "Product" (ARTNR) are found in the SELECT command. Both of these characteristics have a very large number of values in the segment table (they are selective fields in the segment table). As a result, the secondary index MANDT, AKTBO, KNDNR, ARTNR, BUKRS, WERKS, VTWEG is also selective, and it is possible to determine the profitability segment number quickly.

SELECT * FROM CE4xxxx

WHERE AKTBO = "X"

AND KNDNR = "          "

AND ARTNR = "                           "

AND FKART = fixed value

AND (all other characteristics specified)

The problem here is that the selective independent fields (in this case maybe the customer group and division) are specified in the selection but do not occur in the secondary index used. On the other hand, the comparatively less selective fields -- MANDT (usually one value), AKTBO (always "X"), KNDNR and ARTNR (always blank), BUKRS, WERKS and VTWEG (usually only a few different values) -- are contained in the secondary index. When the system reads this index (index range scan), it also has to read all the records of the segment table which contain the specified values for MANDT, AKTBO, KNDNR, ARTNR, BUKRS, WERKS, and VTWEG. In a worst case scenario, this could even be the entire segment table. As a result, the system may read far too much, making the request extremely slow. This in turn affects the runtimes for billing documents considerably.

In the above example (actual data at the customer/product level and planning at the customer group/division level), a secondary index consisting of MANDT, AKTBO, KNDNR, ARTNR, KDGRP, SPART would be sufficient. If you also need profitability segments at higher levels (for example, due to cost center assessment), you need to extend this index again.

It does not make sense to create additional indexes (e.g. an index 1 with MANDT, AKTBO, KNDNR, ARTNR and index 2 with MANDT, AKTBO, KDGRP, SPART) instead of extending the first index. Since the selection condition for finding the profitability segment number is always completely specified, the system always uses the same index.

SAP strongly recommends that you replace the SAP example secondary index (index code 001) with a secondary index which is optimized for your particular circumstances.