Hi,
SAP ECC 6.0
ORACLE release 11.2.0.3.0
in a z report i have this select that i need to improve:
SELECT SINGLE MKPF~XBLNR MSEG~DMBTR MSEG~TBNUM MSEG~TBPOS MSEG~SGTXT
FROM MKPF INNER JOIN MSEG
ON MKPF~MBLNR = MSEG~MBLNR AND MKPF~MJAHR = MSEG~MJAHR
INTO (WA_INSPLOT-XBLNR, WA_INSPLOT-DMBTR, WA_INSPLOT-TBNUM, WA_INSPLOT-TBPOS,
WA_INSPLOT-SGTXT)
WHERE MKPF~MBLNR EQ WA_INSPLOT-MBLNR
AND MKPF~MJAHR EQ WA_INSPLOT-MJAHR
AND MSEG~ZEILE EQ WA_INSPLOT-ZEILE.
i´ve read OSS Note 129380:
Note 1293807 - Case Studies for
Application Developement on Oracle DBs
According to this note there are two possible ways to approach the task:
1.- use the redundant fields of MKPF available in MSEG
e.g.
SELECT SINGLE xblnr_mkpf dmbtr tbnum tbpos sgtxt FROM mseg
INTO (wa_insplot-xblnr, wa_insplot-dmbtr, wa_insplot-tbnum, wa_insplot-tbpos,
wa_insplot-sgtxt)
WHERE mblnr EQ wa_insplot-mblnr
AND mjahr EQ wa_insplot-mjahr
AND zeile EQ wa_insplot-zeile.
IF wa_insplot-xblnr IS INITIAL.
SELECT SINGLE xblnr FROM mkpf INTO wa_insplot-xblnr
WHERE mblnr EQ wa_insplot-mblnr
AND mjahr EQ wa_insplot-mjahr.
ENDIF.
Here as you can see i access only MSEG because field MKPF-XBLNR already exists in MSEG-XBLNR_MKPF.
But performance doesn´t improve much.
2.- use "Delayed table access"
e.g.
SELECT SINGLE kt~xblnr st~dmbtr st~tbnum st~tbpos st~sgtxt
INTO (wa_insplot-xblnr, wa_insplot-dmbtr, wa_insplot-tbnum,
wa_insplot-tbpos, wa_insplot-sgtxt)
FROM
( ( (
mkpf AS ki INNER JOIN
mseg AS si ON
ki~mandt = si~mandt AND
ki~mblnr = si~mblnr AND
ki~mjahr = si~mjahr ) INNER JOIN
mkpf AS kt ON
ki~mandt = kt~mandt AND
ki~mblnr = kt~mblnr AND
ki~mjahr = kt~mjahr ) INNER JOIN
mseg AS st ON
si~mandt = st~mandt AND
si~mblnr = st~mblnr AND
si~mjahr = st~mjahr AND
si~zeile = st~zeile )
WHERE
ki~mblnr = wa_insplot-mblnr AND
ki~mjahr = wa_insplot-mjahr AND
si~zeile = wa_insplot-zeile.
Here some indexes need to be created.
I really do not understand this part of the OSS Note.
What should i do here? Can someone help me undertand it?
Best regards.