Regarding Indexes and performance tuning.

[Former Member]

Hi Everyone,

I need some elaborate explanation about indexes and performance tuning.

1. How do you find out whether the select query which I write is utilizing the indexes .

2. Is it true that the sequence in which the indexes are defined in se11

for eg: MANDT

KNUMH

KOPOS

your select query should also have the same sequence in the where clause else the indexes are not utilized well .

3. Is there any precautions/ special method to write select queries for proper utilization of indexes.

Thanks to all reading and answering in advance.

Rgds,

Anu.

[Former Member]

Hi Everyone,

I need some elaborate explanation about indexes and performance tuning.

1. How do you find out whether the select query which I write is utilizing the indexes .

2. Is it true that the sequence in which the indexes are defined in se11

for eg: MANDT

KNUMH

KOPOS

your select query should also have the same sequence in the where clause else the indexes are not utilized well .

3. Is there any precautions/ special method to write select queries for proper utilization of indexes.

Thanks to all reading and answering in advance.

Rgds,

Anu.

[Former Member]

Hi Anu,

1. You can find it out using st05.

2. yes it is true in mostly of the cases. but i have come across situations where the sequence doesnt matter. but it is always better to specify the index in the same seq as it is mentioned.

3. go thru this: http://sap.ittoolbox.com/documents/popular-q-and-a/specifying-the-index-to-be-used-2462

• reward if helpful.

regards,

madhu

[Former Member]

Hi Madhu,

when I was analysing with st05 its giving me possible indexes, however can you plz elaborate on as how to find out indexes being used or not being used.

Rgds,

Anu.

[Former Member]

Use the "explain" function in ST05.

Rob

[Former Member]

Hi,

Always it is not mandatory that an index should have all the key fields of a table. To see the index of a table

goto SE11->specify table name->click on the indexes... button on the application toolbar.

Based on your requirement you can you any of those index fields in the where clause of your query. Always its a better practice to use the index fields in the order specified. While selecting the records from a table it is always better to select the fields in the same order as specified in the table.

When executing your program, if you want to know which index is hitting

goto ST05->Activate SQL Trace -> execute your program -> deactivate SQL trace -> Display trace.

Now you can see the list of database queries executed before the trace is deactivated. click on any of those sql statements and click on Explain button on the application toolbar. Now under heading Execution plan, if you can see INDEX RANGE SCAN row then it means that you program is hitting an index. click on the text INDEX RANGE SCAN to see the index fields. don't click on TABLE ACCESS BY ROWID, that will give you possible indexes.

Regards,

Venkat.