Index creation

[Former Member]

Hi,

I want to know what is an index. How will it help in improving the performance. How index can be created for a database table. There is no index created for VBAP table. If we want to create an index for VBAP table can we do it. what is the procedure for doing that. Actually problem in program is there is a select query whick checks whether the sales data exists for the particular material or not.

TYPES: BEGIN OF Y_T_MATERIAL,

IDNRK TYPE IDNRK,

MATNR TYPE MATNR,

END OF Y_T_MATERIAL.

IF NOT y_i_MATERIAL[] IS INITIAL.

LOOP AT y_i_MATERIAL into y_wa_material.

CLEAR VBAP.

SELECT SINGLE * FROM VBAP WHERE MATNR EQ Y_WA_MATERIAL-MATNR.

IF SY-SUBRC NE 0.

DELETE y_i_material WHERE IDNRK EQ Y_wa_MATERIAL-IDNRK

AND MATNR EQ Y_wa_MATERIAL-MATNR.

ENDIF.

CLEAR y_wa_MATERIAL.

ENDLOOP.

ENDIF.

Because of the above select query program is taking so much of time because of this program is going into short dump giving time out error.I have modified the above statements as:

DATA: V_TABIX LIKE SY-TABIX.

IF NOT Y_I_MATERIAL[] IS INITIAL.

SELECT vbeln

posnr

matnr

FROM VBAP into TABLE Y_I_VBAP

for all entries in Y_I_MATERIAL

WHERE MATNR EQ Y_I_MATERIAL-MATNR.

IF SY-SUBRC = 0.

SORT: Y_I_VBAP BY MATNR,

Y_I_MATERIAL BY MATNR.

ENDIF.

Loop at Y_I_material INTO Y_WA_MATERIAL.

V_TABIX = SY-TABIX.

read table Y_I_VBAP INTO Y_WA_VBAP with key matnr = Y_WA_MATERIAL-MATNR.

if sy-subrc ne 0.

DELETE Y_I_MATERIAL INDEX v_tabix.

ENDIF.

ENDLOOP.

ENDIF.

But still i think this will give a problem. if we create an index for the VBAP table will this solve the problem?

please help as this is urgent requirement.

[Former Member]

Hi,

An index is a copy of a database table that is reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example, when using a binary search. A table has a primary index and a secondary index. The primary index consists of the key fields of the table and is automatically created in the database along with the table. Secondary Indexes are necessary if the table is frequently accessed in a way that does not take advantage of the primary index. Different indexes for the same table are distinguished from one another by a separate index name. The index name must be unique. Whether or not an index is used to access a particular table, is decided by the database system optimizer. This means that an index might improve performance only with certain database systems. You specify if the index should be used on certain database systems in the index definition. Indexes for a table are created when the table is created (provided that the table is not excluded for the database system in the index definition). If the index fields represent the primary keys of the table, that is, if they already uniquely identify each record of the table, the index is referred to as an unique index.

Procedure :

1. Choose the Indexes tab.

2. To create an index, choose New.

3. Enter a name for the index. Index names, like table names, also have a prefix, followed by an underscore.

If the name of an index was registered on the name server, it cannot be deleted.

4. To select table fields, choose New.

5. if the index is a unique index,

6. If the index is used for all databases, choose and whether it is to be created for all databases. Choose the appropriate checkboxes.

7. Choose File® Save All Metadata.

Primary index : Its the index which is automatically created for the PRIMARY KEY FIELD(S) of the table.

The primary index is always created automatically in the R/3 System. It consists of the primary key fields of the database table. This means that for each combination of fields in the index, there is a maximum of one line in the table. This kind of index is also known as UNIQUE.

Secondary index : Its created as and when required,

based upon other field(s) of the table,

on which search criteria is used in sqls.

Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible. The fewer table entries that can be selected by a certain column, the higher that column’s selectivity. Place the most selective fields at the beginning of the index. Your secondary index should be so selective that each index entry corresponds to at most five percent of the table entries. If this is not the case, it is not worth creating the index. You should also avoid creating indexes for fields that are not always filled, where their value is initial for most entries in the table.

[Former Member]

Hi RAMYA,

Try the following link.

Link: [http://help.sap.com/saphelp_47x200/helpdata/en/4f/991f82446d11d189700000e8322d00/frameset.htm]

Reward if helpful

[Former Member]

"Instead of using VBAP table for checking whether the sales data exists for the 
"particular material or not, try querying VAPMA table. This has a primary index on 
"material number.

Hope this helps.

Thanks,

Balaji