Skip to Content
0
Former Member
Mar 21, 2009 at 10:25 AM

Oracle Hint on Table MKPF and MSEG : force index MSEG~M to be utilize

745 Views

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:

http://rapidshare.com/files/211740852/ZDXX0028.txt.html