Multi Join inner condition - performance tuning - index usage

[srikanth_sandha]

Hi,

I have a question on how the index is being used when there is a multiple joins with their own specific join conditions.

[http://rapidshare.com/files/426099808/sad.zip]

Consider the scenerio,

SELECT MSEG_{BWART MSEG}DMBTR MSEG_{LBKUM MSEG}MATNR

MSEG_{MBLNR MSEG}MENGE MSEG_{MJAHR MSEG}SALK3

MSEG_{UMWRK MSEG}WERKS MSEG_{XAUTO MSEG}ZEILE

MARA_{MATKL MKPF}BUDAT MKPF~XBLNR

INTO (PMSEG-BWART, PMSEG-DMBTR, PMSEG-LBKUM, PMSEG-MATNR,

PMSEG-MBLNR, PMSEG-MENGE_301, PMSEG-MJAHR, PMSEG-SALK3,

PMSEG-UMWRK, PMSEG-WERKS, PMSEG-XAUTO, PMSEG-ZEILE,

PMSEG-MATKL, PMSEG-BUDAT, PMSEG-XBLNR)

FROM ( MSEG INNER JOIN MARA

ON MARA_{MATNR = MSEG}MATNR

INNER JOIN MKPF

ON MKPF_{MBLNR = MSEG}MBLNR

AND MKPF_{MJAHR = MSEG}MJAHR )

WHERE MSEG_{MATNR = MARA}MATNR

AND MSEG~WERKS = FRWERKS

AND MSEG~BWART IN ('890', '890')

AND MSEG~UMWRK = TOWERKS

AND MSEG~XAUTO = ''

AND MARA~MATKL IN MATKL

AND MKPF~BUDAT IN DATUM.

APPEND PMSEG.

CLEAR PMSEG.

ENDSELECT.

1)Indexes MSEG_0,MSEGM and MSEG~Z02 and access path used is attached

2) There are few queries which follow the same accesspath but with different index based on the data request in the select query and the variants.

3)MSEG_{0 doest have MATNR and MSEG}M and MSEG~Z02 also dont have MBLNR ( high distinct key).

4)MSEG~Z02 is not following the stadard format of the index ( no mandt).

5)MSEG is a partitioned table with partitioned indexes.

Now to my knowledge, for every join query the processing will be fast if join conditions or filters be included in the index. As a high distinct column would be used as a portioning key column.

question 1) from ABAP query, first join condition is MSEG INNER JOIN MARA ON MARA_{MATNR = MSEG}MATNR Let us say,

the index used is MSEG_{M in the access path. So while doing the second inner join INNER JOIN MKPF ON MKPF}MBLNR = MSEG_{MBLNR AND MKPF}MJAHR = MSEG~MJAHR.

Is this MSEG~M will be utilized for this scenerio as well ( as there are no other index mentioned in the access path).

If so, as the MBLNR is not added in MSEG~M doesn't this create huge cost to the system ?

Question 2) From the SQL query, we find that ABAP is just parsed the joins in to simple 'and' conditions, could some one tell me how index is selected here. I do agree it is based up on the variants but including the second join conditions in to index would improve the performance. ?

Please let me know if you need any more information. You can find the index images at the rapidshare link.

Thanks and Regards,

Srikanth

[lbreddemann]

> question 1) from ABAP query, first join condition is MSEG INNER JOIN MARA ON MARA_{MATNR = MSEG}MATNR Let us say,

> the index used is MSEG_{M in the access path. So while doing the second inner join INNER JOIN MKPF ON MKPF}MBLNR = MSEG_{MBLNR AND MKPF}MJAHR = MSEG~MJAHR.

> Is this MSEG~M will be utilized for this scenerio as well ( as there are no other index mentioned in the access path).

> If so, as the MBLNR is not added in MSEG~M doesn't this create huge cost to the system ?

1. Indexes are DB-storage level objects. That is, they are relatively low-level in the database.

2. ABAP is way above the database.

3. It's apples and oranges to look at a ABAP SQL and talk about index usage!

Show us the SQL that the dbsl made out of your ABAP.

Then we can probably talk about whether or not an index would be usefull.

> Question 2) From the SQL query, we find that ABAP is just parsed the joins in to simple 'and' conditions, could some one tell me how index is selected here. I do agree it is based up on the variants but including the second join conditions in to index would improve the performance. ?

You want to discuss index usage decisions made by the Oracle CBO but you don't know that there are two ways to declare a join and that these are equal?

regards,

Lars

[srikanth_sandha]

Hi Lars,

Thanks for replying, I have updated the DBSL query and its access path and the index details at the [http://rapidshare.com/files/426099808/sad.zip]

http://rapidshare.com/files/426099808/sad.zip

Please let me know if you need any information. My question is does the performance improve if we add MBLNR in the index MSEG~M( As per the Partitioned table concepts and SQL basic rule that Join conditions columns should be in Index apart from the where clause columns and the parsed sql query).

OR the ABAP works the other way ? first bringing up the MSEG data based on MSEG_{M, and then the MARA data as per Index MARA}0 then comparing both. then bringing MKPF data by index MKPF~0 ?

waiting for your reply.

Thanks and Regards,

Srikanth

[lbreddemann]

HI there,

how about update statistics and or rebuild the indexes?

And please post the index information and explain plan in the forum message.

I'm not going to download anymore files.

regards,

Lars