MSEG: how does the database optimizer use indexes?

[Former Member]

Dear All,

I am trying to improve the speed of transaction MB51. The standard solutions did not really help. That is why I decided to create additional indexes, but I have some doubts how the database optimizer uses indexes in special cases.

Earlier I found a thread on one of the SDN forums where the developer said the order and number of the fields in the where clause must exaclty fit to the order and number of the fields in the index, otherwise the index is either not used or just partially used. I do not want to make any unnecessary attempt in the system, that is why I turned to you! I have an exact example.

I have two cases. I want to select data based on:

1. plant, storage location, movement type

2. plant, movement type

(I left the posting date blank and choosed the radiobutton "database determines optimum access".)

I analysed the result in ST05 in both cases. The where clause of select statements look as follows:

1. WHERE T_00 . "MANDT" = ? AND T_01 . "BWART" = ? AND T_01 . "LGORT" = ? AND T_01 . "WERKS" = ?

2. WHERE T_00 . "MANDT" = ? AND T_01 . "BWART" = ? AND T_01 . "WERKS" = ?

According to my current knowledge I should create 2 different indexes with different order of fields.

1. MANDT BWART LGORT WERKS

2. MANDT BWART WERKS

What happens if I create only one index with order of fields as follows? MANDT BWART WERKS LGORT

Do you think the database optimizer is intelligent enough to reorganize the where clause to use this index in both cases and the result will be as fast as it would be in case of 2 indexes?

Or do you have better idea?

We use MaxDB and SAP ECC 6.0

Thanks for your reply in advance!

Rgds,

Viktor Bojtos

Hungary

[ThomasZloch]

Earlier I found a thread on one of the SDN forums where the developer said the order and number of the fields in the where clause must exaclty fit to the order and number of the fields in the index, otherwise the index is either not used or just partially used

Not true, one of the many "legends".

Depending on how critical this is you might still need both indexes though, because the access path via BWART / LGORT or BWART / WERKS might be significantly different depending on selectivity of LGORT and WERKS (how many different distinct values can these fields have?)

If in doubt, test the effects in a sandbox with plenty of data.

Thomas

[Rui_Dantas]

>

> *Do you think the database optimizer is intelligent enough to reorganize the where clause to use this index in both cases? 
> Viktor Bojtos
> Hungary

Short answer to your question: yes, it is intelligent enough.

The order of the fields in the where clause is not relevant when the optimizer chooses the correct index.

Regards,

Rui

[HermannGahm]

> Do you think the database optimizer is intelligent enough to reorganize the where clause to use this index in both cases and the result will be as fast as it would be in case of 2 indexes?

absolutely!

The order of the fields in the where condition do not matter.

The order of the fields in the index matter if we have ranges or rer.selectivity or depending on OTHER selects... in your case the suggested index will support both statements optimally since we have only EQUAL and AND cominations.

Kind regards,

Hermann

[Former Member]

Hi Hermann,

I completely agree with you. I have infact observed this behaviour in a few programs in my previous assignments which involve processing of large amounts of data. I have also proposed the same thing in my office but it is a strict rule here to have the fields in the SELECT and the WHERE condition in the same order as it is present in the table. To prove our point we can write a program but it cannot be shown as concrete evidence because anything related to database performance would also depend on other parameters apart from our program. Only concrete evidence would be an SAP note. Can you please tell me if there is any SAP note which says the same thing as you do or even anything close to what you say? If there is none available can you please let me know the procedure of acquiring an SAP note which addresses this issue directly?

Thanks and Regards,

Shashank...

[yuri_ziryukin]

 Only concrete evidence would be an SAP note. Can you please tell me if there is any SAP note which says the same thing as you do or even anything close to what you say? If there is none available can you please let me know the procedure of acquiring an SAP note which addresses this issue directly?
> 
> Thanks and Regards,
> Shashank...

Hello Shashank,

this is in fact not related to SAP systems. It is the functionality of the DB (Oracle, MS SQL, etc.) to find the correct index based on the where clause. And as several people confirmed in this thread, it does not matter in which order the fields are specified in the where clause.

For all people working closely with databases this is something like mathematical axiom and does not need to be proven with something like an SAP note

Cheers,

Yuri

Edited by: Yuri Ziryukin on May 9, 2011 9:58 AM

[Former Member]

Well, reading your replies let me correct myself!

I might happend that we use multiple values instead of single values as follows:

1. WHERE T_00 . "MANDT" = ? AND T_01 . "BWART" IN ( ? , ? ) AND T_01 . "LGORT" IN ( ? , ? ) AND T_01 . "WERKS" IN ( ? , ? )

2. WHERE T_00 . "MANDT" = ? AND T_01 . "BWART" IN ( ? , ? ) AND T_01 . "WERKS" IN ( ? , ? )

If I understood well, based on Hermann's reply in this case the order of fields DOES matter! Am I right?

Viktor

[Former Member]

Reread his answer. I don't think that's what he said.

Rob

[Former Member]

I am sorry. Maybe I misunderstood something.

Hermann said that the suggested index will support both statements if we have only EQUAL, but sometimes we have IN as well. That is why I corrected my original message in my second post.

By the way, what does the "rer.selectivity" mean?

Viktor

[HermannGahm]

Hi,

>but sometimes we have IN as well.

and a INLIST is nothing else than a repeated equal...

a = :a0 and b in (:a1, :a2) and c = :a3

is the same as:

( a = :a0 and b = :a1 and c = :a3 ) OR ( a = :a0 and b = :a2 and c = :a3)

databases use or concatenations or inlist iterations or nested loop joins for these....

>By the way, what does the "rer.selectivity" mean?

this was a typo... i meant regarding selectivity... selectivity for the fields and their order in the index do matter especially in combination with ranges... if we only have AND and EQUAL (or IN) it doesn't....

Kind regards,

Hermann

[Former Member]

Dear Hermann,

Thank you for your kind answer! I think you have completely answered my basic question, but in order to understand everything fully in your answer, let me ask one more thing. Maybe my knowledge is not deep enough. Could you explain briefly what you meant when you wrote RANGES? In my knowledge the range is a special variable, actually an internal table.

( In order to avoid any misunderstanding I paste the original select statement from ABAP here, which looks as follows:

where MSEG~AUFNR in AUFNR

and MKPF~BUDAT in BUDAT

and MSEG~BWART in BWART

and MSEG~BWTAR in BWTAR

and MSEG~CHARG in CHARG

and MKPF~CPUDT in CPUDT

and MKPF~CPUTM in CPUTM

and MSEG~EBELN in EBELN

and MSEG~EBELP in EBELP

and MKPF~FRBNR in FRBNR

and MSEG~KUNNR in KUNNR

and MSEG~KZBEW in KZBEW

and MSEG~KZZUG in KZZUG

and MSEG~LGORT in LGORT

and MSEG~LIFNR in LIFNR

and MSEG~SOBKZ in SOBKZ

and MKPF~USNAM in USNAM

and MKPF~VGART in VGART

and MSEG~WERKS in WERKS

and MKPF~XBLNR in XBLNR)

Thank you in advance!

Rgds,

Viktor

[HermannGahm]

Hi,

> Could you explain briefly what you meant when you wrote RANGES? In my knowledge the range is a special variable, actually an internal table.

that's the ABAP range, that's right. I was talking about range scans.... with conditions like :a0 BETWEEN :a1 or

... >= :a3 or... ... <= :a4 or ... .... LIKE :a5 ... these conditions may hit more than one value of a column... in such cases the order of the fields in the index do matter....

if you have lets say

a = :a0 AND b = BETWEEN :a1 AND :a2 and c = :a3

your index should not start with column b... because you may have to scan the whole (or large parts of it) index in case the between covers all the values (or large parts of it)...

Kind regards

Hermann

[Former Member]

I think I understood the point!

Thank your for your kind support!

Rgds,

Viktor