Hi Experts, I'm quite new to ABAP performance tuning, I try to put details as much as I can in my question below, just to have a quick overview of my problem below, I want to use/force in my SQL below to force the utilization of index MSEG~M.
added an Oracle Hint forcing index MSEG~M
SELECT MATNR BWART MENGE
FROM MKPF
INNER JOIN MSEG
ON MKPFMANDT EQ MSEGMANDT
AND MKPFMBLNR EQ MSEGMBLNR
AND MKPFMJAHR EQ MSEGMJAHR
INTO CORRESPONDING FIELDS OF TABLE T_MSEG2
WHERE MKPF~BUDAT IN S_GSTRP "(index bud should be used)
AND MSEG~MJAHR IN R_DOCYEAR
AND BWART IN ('931', '932',
'901', '902',
'701', '702',
'703', '704',
'707', '708',
'711', '712',
'713', '714',
'715', '716',
'717', '718',
'551', '552',
'553', '554',
'555', '556') "ZZCHK: Constants Not Applicable
AND MATNR IN R_MATNR3
AND MATNR IN R_COMPNTS
AND WERKS EQ P_WERKS
%_hints oracle u2018INDEX(u201CMKPFu201D u201CMSEG~Mu201D)u2019.
****************************************************************
Note: I'm assuming that you'll take not some comments on further enhancement:
- " INTO CORRESPONDING FIELDS "
- use of field symbols
- I didn't modify it yet since my focus if for MSEG~M
- cannot make material number as mandatory field
****************************************************************
Objective: Force to use index MSEG~M
Problem:
After adding the Oracle Hint, I did the analysis in ST05 and it seems like index M is still not being forcefully
utilize instead MSEG~0 is being used.
I replaced the Oracle Hint with:
%_hints oracle u2018INDEX(u201CT_01u201D u201CMSEG~Mu201D)u2019.
In ST05, I can get see that index MSEG~M is now being utilize BUT worst thing happened after applying this, the program timed out.
I already take a look on the following Notes: 129385, 130480, 772497, 162034, 818321 but nothing seems to be helpful in my problem.
Questions:
1. In syntax %_hints oracle u2018INDEX(u201CT_01u201D u201CMSEG~Mu201D)u2019. T_01 stands for MKPF right which is the first table, do you what's the reason for the program time out after adding this Oracle Hint? Is this the proper syntax?
2. How can I force in my code to use index MSEG~M without any program timeout?
For the whole source code of my question above, please see the link: