Consider a situation where queries need to target a set of fields in MARA which are not part of the primary index. Based on a set of key fields and pointers to non-key fields, primary index enables quick retrieval of data from database table. However, the situation becomes a bane if the query frequently accesses non-key fields without any primary key advantage. Hence, the secondary index is usually created to meet the requirement.
Following are the steps to create a secondary index –
- Goto TCode SE11 and open relevant database table
- In menu, Goto-> Indexes – check if any secondary index already exists.
- If desired secondary key not present, using create button, select Create Index option.
- Add name and details of the index.
- Save and activate.
It is noteworthy that for any table at a maximum of 9 secondary indexes can be created to supplement the primary index. This is very important because a large number of indexes are detrimental to the performance of the table. Hence, the ability to add a secondary index is highly restrictive. Choice of fields and type of table are two important factors that determine whether one should opt for secondary index or not.
The fields were chosen for the secondary index should restrict the resulting data as much as possible. If the field is not selective enough, it should not be a part of the secondary index. Also, if a data is frequently modified in a table, it is usually not a good idea to have multiple secondary indexes on such tables.
Is using secondary index a good idea? If the secondary index is working well with the ABAP optimizer and other factors are taken into account, the secondary index can be a great optimization tool. Traces can be helpful to see the improvement in performance and suitable choice can be made.