Problem with Secondary Index

[Former Member]

Dear Experts,

I have one performance improvement issue .

For that I created non-unique secondary Index (and adjuted the same in SE 14 ) on table MBEW on fields MANDT , MATNR , BWKEY , LFMON , LFGJA ...because these fields are there in my where clause.

On the same fields I created one more non-unique secondary Index on table MBEWH...for the same purpose..

But it takes around 10000 seconds to execute while I run it in background after creating those secondary Indices (Even before creating secondary indices it took 10000 seconds ).

So there is no improvement in performance....

How can I check if my secondary indices are really used or not ?

And if they are not being used what can be alternative for performance improvement ?

Do we have to keep the same order of fields in Where clause while creating secondary index ?

Any suggestions most welcome........................

[ThomasZloch]

> How can I check if my secondary indices are really used or not ?

Switch on SQL-Trace via ST05, run the pogram, switch trace off and analyse the "explain"

> And if they are not being used what can be alternative for performance improvement ?

List your open SQL statements here for further analysis.

> Do we have to keep the same order of fields in Where clause while creating secondary index ?

No, that's just a legend. The index should be designed to provide maximum selectivity top-down, and the fields should show up in the WHERE-clause, but not necessarily in the same sequence (it does not hurt, of course).

Thomas

[former_member194613]

please read in wikipeadia or similar about cost based optimizers.

Your index is not taken because your new index does not have any statistics!

In the SQL-Trace you can go to the explain of the SELECT, there you see the indexes, with

some databases you get a graphical layout. Double-click the colored table name, then

you see all indexes, and fields and date of statistics. There you can update the statistics.

Siegfried

[ThomasZloch]

Plus, I just realised that your index also starts with MANDT and MATNR, just as the primary key of MBEW. As MATNR is normally a highly selective field (many distinct values, few rows for each distinct value) I doubt whether your new index provides an advantage with the fields in that sequence.

The question is also, is there a narrow selection range for any of these fields? If all are optional ranges and the user does not fill any of them, then an additional index is pretty much useless.

Thomas

[Former Member]

Also, in addition to the above suggestions, pls check if the fields in the where conidtion are in same order as the index created at database level. Else it will not select the respective index during the selection.

[former_member194613]

> Also, in addition to the above suggestions, pls check if the fields in the where conidtion

> are in same order as the index created at database level. Else it will not select the

> respective index during the selection.

this argument can be found soemtimes, however it is completely incorrect. The order of the

fields in irrelevant.

However, for another reason it is wanted that statement look similar, best way to achive that is to

follow the order of the fields in the table. But this is a nice-to-have feature, not mandatory.

Siegfried

[Former Member]

Use SQL-Trace(Transaction) ST05 and perform the analysis of the Select statement.

Edited by: harsh bhalla on Mar 21, 2009 10:06 AM

[Former Member]

Hi,

It must verify that the index exists in the Data Base. Execute the SE11 transaction with the MBEW and MBEWH tables and presses de INDEXES button, double click in the index and check status "Index xxxx exists in database system xxxxxx" or "Index does not exist in database system xxxxxx".

hope this information is help to you.

Regards,

José

[Former Member]

done .