Table - INDEXES (SE11)

[Former Member]

Hi All,

I am trying to query the RESB table with the field EBELN, but i find the response time very long, but when i try the same with AUFNR field the response time is very fast.

In SE11 in INDEXES i find an INDEX maintained for AUFNR. I believe this is ensuring a fast response. If i add a custom index with the fields MANDT, EBELN, EBELP will the response time improve?

Is there any other parameters which i need to keep in mind, also if i create this index will it cause any problem to the system or anyone trying to access this table?

Await inputs.

Regards,

Vivek

[Former Member]

Hi Vivek,

One precaution you must taken when creating an index is to ensure that the subset tables (buffers) generated by the indexes are small.

If e.g. you create a buffer on a table using a combination of fields which are not very unique, e.g. in EKPO you create a index on WERKS (plant) you would have thousands of records for the same plant, however if you create a index on WERKS (plant) + Storage location (LGORT) then you would get less records for the combinations and hence a better indexing.

To gauge this for your index fields, run transaction DB05.

Enter the Table name and select "Analysis for Specified fields" and run the application. It would create a background job. Check the result of the job (spool).

The figures returned i.e. "Number of areas that are specified by the generic key and contain the given number of rows" should fill more of the columns from the left, i.e. signify the combination returns more unique records.

Cheers,

Aditya

[Former Member]

Hi Vivek

We have also created a secondary index with MANDT , EBELN , EBELP ...make sure you choose the option non-unique index and index on all database systems. Also you must inform the BASIS about this index creation .

Thanks and Regards

Shivika

[Former Member]

Hi Aditya & Shivika,

Thank you both for your quick inputs.

Note for Shivika: After creating the index for EBELN, EBELP... did you face any issues while accessing this table from other programs or reports?

Note for Aditya: As per your advice, i have created an index with MANDT, EBELN, EBELP, WERKS & in DB05 i notice significant improvement in performance.

Also would like to know,

1. What will be the impact if in case i delete this index at a later date?

2. What would be the advisable number of indexes that can be created for a table of the size or RESB?

Await Inputs.

Vivek

[Former Member]

Hi,

What to Keep in Mind for Secondary Indexes

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. This can best be shown using an example.

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.

The following selection is frequently made on address file ADRTAB:

SELECT * FROM ADRTAB WHERE TITEL = ‘Prof.’ AND NAME = X AND VORNAME = Y.

The field TITLE would rarely restrict the records specified with NAME and FIRSTNAME in an index on NAME, FIRSTNAME and TITLE, since there are probably not many people with the same name and different titles. This would not make much sense in this index. An index on field TITLE alone would make sense for example if all professors are frequently selected.

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.

The database system sometimes does not use a suitable index for a selection, even if there is one. The index used depends on the optimizer used for the database system. You should therefore check if the index you created is also used for the selection (see How to Check if an Index is Used).).

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.

Reward if found helpfull,

Cheers,

Chaitanya.

[Former Member]

Hi Chaitanya,

Thank you for the comprehensive answer, can you just clarify the point, what would be the advisable limit for the number of indexes on a table like RESB.

At the moment i see 3 indexes in RESB table, one created by SAP & the other 2 are created by users. Out of these 2, one is MANDT - AUFNR - VORNR - XLOEK & the other is MANDT - EBELN - EBELP - WERKS (created by me).

1. Would it be ok to have these 3 indexes?

2. what if any will be the impact if i delete the index created by me at a later stage?

Await your inputs, so i can reward the correct points & close this thread.

Regards,

Vivek