on 10-20-2010 6:42 AM
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 MSEGBWART MSEGDMBTR MSEGLBKUM MSEGMATNR
MSEGMBLNR MSEGMENGE MSEGMJAHR MSEGSALK3
MSEGUMWRK MSEGWERKS MSEGXAUTO MSEGZEILE
MARAMATKL MKPFBUDAT 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 MARAMATNR = MSEGMATNR
INNER JOIN MKPF
ON MKPFMBLNR = MSEGMBLNR
AND MKPFMJAHR = MSEGMJAHR )
WHERE MSEGMATNR = MARAMATNR
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 MSEG0,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)MSEG0 doest have MATNR and MSEGM 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 MARAMATNR = MSEGMATNR Let us say,
the index used is MSEGM in the access path. So while doing the second inner join INNER JOIN MKPF ON MKPFMBLNR = MSEGMBLNR AND MKPFMJAHR = 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
> question 1) from ABAP query, first join condition is MSEG INNER JOIN MARA ON MARAMATNR = MSEGMATNR Let us say,
> the index used is MSEGM in the access path. So while doing the second inner join INNER JOIN MKPF ON MKPFMBLNR = MSEGMBLNR AND MKPFMJAHR = 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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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 MSEGM, and then the MARA data as per Index MARA0 then comparing both. then bringing MKPF data by index MKPF~0 ?
waiting for your reply.
Thanks and Regards,
Srikanth
could some one reply to my post please.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.