Indexing in SAP Tables

Indexing in SAP Tables

  INDEX

• An Index is a copy of database table having few numbers of fields. This copy is always in sorted form. As we know, Sorted data would always have a speed access from a database table. Hence, we use an index for the table while reading the database records. Index also contains a pointer pointing towards actual database table so that it can access fields that are not contained in the Index. There are two kinds of index for a database table:

• Primary Index
• Secondary Index

 Primary Index:

• The columns in a table have an 'X' under "KEY" in the dictionary table/structure (SE11) for a primary index. They always appear in the initial columns of a table. Or we can say that primary index consists of a primary key field of a database table. Following screen shot shows the primary index of a table MARA.   
  Secondary Index:
         Secondary Index is the one which we create separately for the speed access of a database table. Go to the Transaction SE11 - enter the table name (EX: MARA) and click on Indexes button in the top right corner.

Tables that allow Indexing:

• As mentioned earlier in the abstract, not all the tables allow you to create an Index. They are allowed only for transparent tables. All business data and application data are stored in transparent tables.

Ex: VBPA (Sales Document: Partner), VLCVEHICLE (VELO: Vehicle), MARA (General Material Data), etc..

• Secondary indexes are not allowed for the below tables but gives a message saying 'Index maintenance only possible for transparent tables '.                       

-> Cluster tables.   Ex: BSEG (Accounting Document Segment),   KONV (Conditions (Transaction Data) ), etc..

-> Pooled tables.    Ex: Configuration tables, etc..

Before creating an index:

These are the points to be remembered before creating an Index.

•  Create Secondary Indexes for the tables that you mainly read. Because every time we update a database table, it would update indexes also. Let's say there is a database table where we create (or update) 100s of entries in a single day. Avoid using Indexes in such cases.     
• We should take care that an index shouldn't have more than 4 fields and also the number of indexes should not exceed 5 for a database table. Or else, it would result in choosing a wrong one for particular selection by an optimizer.
• Place the most selective fields at the beginning of an Index.
• Avoid creating an Index for a field that is not always filled i.e., if it's value is initial (null) for most entries in a table.

Create Index:
        1)  STEP 1:  In the maintenance screen of a table (SE11), click on the Index button in edit mode.   
        2)  STEP 2:  If already an Index exists then it would list them. If not, it would ask us to create an Index. Click on Yes button.
        3)  STEP 3:  Then it would ask for a unique Index ID. This Index ID will identify the respective Index of a database table. We   can give digits or numbers in the Index ID field. Index ID '0' is reserved for primary Index. Let us enter an ID similar to the field which we are using in INDEX.  Here, I've chosen ID as SAP.
        4) STEP 4: You will be going inside a screen called maintenance Index. Here, we provide the short description and then chose the options provided (Refer next slide). Then we enter the field under the field name. It would automatically populate the short text beside the field name when you press enter.   

 

•         Options:

1.  Unique Index: If the fields chosen in an index are very unique i.e., for each combination of fields only one entry exists, then we chose unique index. 
2.  Non-Unique Index: If the above is not true, we choose this option. We have 3 radio buttons under this called Index on all database systems, For selected database systems, and No database index. We can chose any one of them depending on our requirement. 
3. For client Dependent tables, we always include the field "MANDT' under all the indexes as you can see in the previous slide's screenshot. Or else, Optimizer might not use the Index. 

After creating an Index

         These are the points to be remembered while coding in ABAP programs for effective use of Indexes i.e., to avoid the full table scan. 

• In the select statement, always put the condition fields in the same order as you mentioned in the INDEX. Sequence is very important here. 
• If possible, try to use positive conditions such as EQ and LIKE instead of NOT and IN which are negative conditions. 
• Optimizer might stop working if you use OR condition. Try to use IN operator instead of that. 
• The IS NULL operator can cause a problem for the Index as some of the database systems do not store null values in the Index structure. 

How to check whether Index is being used:

       Sometimes, suitable index is not used for a selection, even if one exists. The following procedure tells us how to find out which index is being used for the selection. 

         Procedure:

           1) Open a transaction called ST05 in a separate session. This is used to analyze the SQL trace. 

            2)      Now, Open the program (SE38) in a separate session and put a break point at the select statement where you would like to find out the index being used.  

Next, we execute the program and it stops at the break point. Then, you click on trace-on in the ST05 transaction. Press F5 (debugging) in the program to execute that select statement. After executing the select statement, click on trace-off in the ST05 transaction.
            3)     Next, Click on Trace list which displays basic trace list of the select statement we executed. We can find out which index being used by choosing the function EXPLAIN SQL after putting the cursor on one of statements (PREPARE, OPEN, REOPEN). 

         4)      We get the complete execution plan for the SQL statement. This will tell you about which index is being used. In the below screenshot, it shows that Index Z03 is being used from the table VLCVEHICLE and the index T for the table MARA. Clicking on any one of these indexes would give you detailed information on that particular Index.