Indexes are part of the database management system and they can seriously improve the performance of your reports. They can be defined/added to a table through the data dictionary (SE11) and they require no actual changes to the report or reports you are targetting for your performance improvement. Before you add your index - read this article. Learn a bit about it. An index is a life-long task ongoing task for the system to keep up to date.

An index is part of the database system

Indexes are not specific to an Abap report. In fact - the only relation is that the table that is selected on in the Abap report is "optimized" to cater for a type of selection on the table. And that's what makes "Indexing" something to think about: the fields on an index are all key fields and the index will only be used when all it's fields are defined on the selection request (the SELECT statement). Note that the main key of a table is in fact also an index. The main key fields reside in index 0 or ~0. Why is this interesting information ? Because you should check the usage of indexes (or your own index) in ST05 and the ~0 will indicate the table's own key was used for the selection.

Find the overview of indexes for your table in SE11 Data Dictionary, under button "Indexes", where they are listed with the key fields for the index.

An index is effectively a table with all fields defined on the index and a table record reference to the original table entry. These tables consume space and system performance: as the system will keep track of them.

Indexes are serious business

The monitoring tool clearly show it: tables have their size and will occupy space. Indexes do the same. There are tables for which the indexes consume more space on the system's database, than the table itself. Then there is the simple fact that the system will keep an index up to date. Fully automatically, with every table update/insert/delete there is. So instead of inserting a record into a table, a record is in fact inserted into the table and all the indexes available for it. This will slow down the update process !

Getting this whole index business wrong - is also serious business. So make sure you get it right. And also: don't assume your predecessors got it all right - look out for custom added indexes, that don't work.

SAP delivers some tables with multiple indexes. Like with every custom object, the indexes that are added on the customer site adhere to the ~Znn convention (~Z01, ~Z02, ...).

Checklist on table indexes

An important item on the use of Indexes is this simple fact: the database will automatically use the index - for which the available field values are used. Thus an index for a single field will be used if a field value for this field is supplied in the selection. So your selection screen has a material number and the selection is on MARA. Index ~0 (also called: the key of the table) will be used when a material number is supplied in the selection screen. As a selection screen is likely to have select-option fields, a speficied range will also work.

1. First of all, find the list of indexes already available and determine why these don't help you with the performance on your report. See the next paragraph on how the use of an index can be made visible.

   When you have decided to add a new index - design it. Which fields should be on your index ?

2. Healthy spread: the fields you want to use for the access to the table, should be specific enough to "cut the table up into small enough chunks". Set up an index on a single field: the deletion indicator (LVORM) and this will be the effect:
   When a selection is done in which the deletion indicator is specified, the (very faulty) index will be selected and the database will utilize it.
   Since the results is a split table, with say 5% entries that were marked for deletion and 95% regular entries, the selection is continued with 95% of the selection (unless you want to select the deleted entries).
   The index will consume as much space as other indexes (mayby a bit less, as there is only 1 short field to keep track) - but no performance improvement gains are to be expected.
   An even spread should at least split the main table into 1% chunks. Thus the results of the index selection should never yield more than 1% of the table content - as answer.
3. Don't put too many fields in your index. In other words: don't overdo it. Considering that every field will need to be specified in the selection, having too many fields will limit the index general use. Do remember: the index is not created for a specific report, it is part of the database and potentially applicable to many reports. It's really OK to put a bit of effort in it!
4. Don't put the information you are looking for - in the index itself. This is a common mistake: you want to get the stock from table MARD and you specify the index with the LABST field. Now the index will only be utilized when the actual value for LABST is supplied in the selection statement.
5. Relation to the table key - are all fields from the index also in the table key ? Then there is no point in having the index (think about this for just a second.. )
6. Status fields - have a "spread" that is not suitable for index fields. Like a deletion indicator or a payment block.
7. Quantity or amount fields - these are fields that are likely to alter often and are functionally unsuitable for index fields. Why would a selection define the actual quantity ?
   Consider this: any table field can be defined as index field. Once on the index, the field becomes a key field. So when quantity or amount (or similar) fields are used as index fields, the system will need to update these indexes with every stock move or amount alteration.
8. Check whether the index you have added to the system is being utilized as expected. It's a waste of system resources if you let a new index into the system - which is not used. Do note: it's quite easy to determine whether an index is used in a specific situation, but it's very tricky to determine where/when indexes are used. As this factually depends on which fields are filled in on the selection screens of report - just as example.

• Prev
• Next