data dictionary

[Former Member]

What is the role of secondary index in performance?

[ak_upadhyay]

Hi,

The Standard Secondary Index

The speed with which the system finds segment numbers in the segment table is extremely important for overall system performance in transferring collective invoices, external data or postings (such as order settlement, direct postings from FI) to profitability segments.

The primary index (index ID 0) to the key of the segment table is not well-suited for the above selection. Consequently, SAP tries to support this special access by means of a suitable secondary index (index ID 1). In the standard system, this index contains some of fixed characteristics which are found in every operating concern:

MANDT

AKTBO

KNDNR

ARTNR

BUKRS

WERKS

VTWEG

Use of indexes for search queries

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.

1: Profitability Analysis at the customer/product level

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.

2: Profitability Analysis at a higher level

Now let us look at a system in which the characteristics "Customer" and "Product" are not segment level characteristics. (See also the chapter Master Data ® Structures ® Define Profitability Segment Characteristics (Segment-Lvl Characteristics) in Customizing).

In this case the fields KNDNR and ARTNR are empty in all the records in the segment level CE4xxxx. As a result, the aforementioned index is not selective. In this case the selection for finding the profitability segment number reads:

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.

These examples yield the following rule:

Rule:

A secondary index is useful for supporting segment number determination if it contains the logically unrelated, restrictive characteristics from the segment table. The fields MANDT and AKTBO must be the first fields in the secondary index, otherwise the database system reads the primary index instead.

3: A well-suited secondary index

If your system uses the characteristics "Customer" and "Product" in the segment level, you should use an index which contains the fields MANDT, AKTBO, KNDNR and ARTNR. On the other hand, if you are not using "Customer" and "Product", and if the customer group (KDGRP) and division (SPART) are the independent characteristics, it would make much more sense to use an index with the fields MANDT, AKTBO, KDGRP and SPART. Thus in order to create a suitable secondary index, you need to know the logical hierarchy of characteristics in your operating concern.

Reward points if useful....

Regards

AK

[Former Member]

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 will ultimately be selected.

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 will quickly find 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 order of the fields in the index is very important for the accessing speed. The first fields should be those which have constant values for a large number of selections. During selection, an index is only of use up to the first unspecified field.

Only those fields that significantly restrict the set of results in a selection make sense for an index.

Additional indexes can also place a load on the system since they must be adjusted each time the table contents change. Each additional index therefore slows down the insertion of records in the table.

For this reason, tables in which entries are very frequently written generally should only have a few indexes.

Creating an additional index could also have side effects on the performance. This is because an index that was used successfully for selection might not be used any longer by the optimizer if the optimizer estimates (sometimes incorrectly) that the newly created index is more selective.

The indexes on a table should therefore be as disjunct as possible, that is they should 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.

Please have a look at below link for these details:

[What to Keep in Mind for Secondary Indexes |http://help.sap.com/saphelp_nw70/helpdata/en/cf/21eb20446011d189700000e8322d00/frameset.htm]

I hope it helps.

Thanks,

Vibha

Please mark all the useful answers

[Former Member]

Secondary keys can be defined for each table to optimize the data access via SQL statements. They can refer to any column combination and they help to prevent sequential scans over the table. Like the primary key, the secondary key can consist of multiple columns.

A secondary key is often called an index (not to be confused with B* index).

[Former Member]

Hi,

If you cannot use the primary index to determine the result set because, for example, none of the fields primary index occur in the WHERE or HAVING clause, the system searches through the entire table (full table scan). For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.

You should only create secondary indexes, for database tables from which you mainly read, since indexes have to be updated each time the database table is changed. secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible to improve performance..

rgds,

bharat.

[b_deterd2]

Hi,

Secondary indexes are only relevant if the first index cannot be followed. SAP then searches for another index which it can follow.

In your abap you should do select statements based on a index.

regards,

bert