Indices and F4_GET_RESULT

[boolan]

Hello,

Introduction:

There is a performance problem with VF03. When You provide only BUKRS(company code) and XBLNR (reference)on the selection screen and press Search, then You must wait several minutes before a screen with 1 selected entry pops up. Data are read from table BKPF.

I have implemented the OSS notes 789098 and 964587 so that the database index BKPF_1 is used, but it obviously isn't. I checked it in the SQL Trace.

Index BKPF_1 comprises fields: MANDT, BUKRS, BSTAT, XBLNR.

After having debugged the VF03 I discovered that the data is retrieved in the funcion module F4_GET_RESULT.

I wrote a small program which retrieves data in the same way as the function module and my program uses index and returns data in a flash.

I don't understand why F4_GET_RESULT does not use the index.

<b>How can one make F4_GET_RESULT use it ?</b>

Here's the program:

-

---

REPORT ZTEST_CURSOR.

DATA: WA_BKPF TYPE BKPF,

IT_BKPF LIKE STANDARD TABLE OF WA_BKPF,

SELTAB(20),

C1 TYPE CURSOR,

CONDITION TYPE STRING.

START-OF-SELECTION.

SELTAB = 'BKPF'.

CONDITION = 'BSTAT = '' '' AND BUKRS = 4005 AND XBLNR LIKE ''0096183992'''.

OPEN CURSOR C1 FOR

SELECT * FROM (SELTAB)

WHERE (CONDITION)

%_HINTS ORACLE 'first_rows' DB6 'OPT_FOR_ROWS 1'.

END-OF-SELECTION.

FETCH NEXT CURSOR C1 INTO table it_BKPF.

loop at it_bkpf into wa_bkpf.

write:/ wa_bkpf-xblnr, wa_bkpf-bukrs.

endloop.

[Former Member]

Are you sure about the WHERE condition. I didn't see a reference to BSTAT whan I ran VF03. That would do it.

Rob

[boolan]

Yes, I am sure.

Please check the OSS notes. I bet they are not implemented in Your system. One of them adds BSTAT = ' ' to the WHERE condition, because that field is present in the index.

[Former Member]

You're right - they're not. Could you please do a performance trace for VF03? This should verify exactly what SQL statement is being generated. If you do an explain on the select, it will show you what (if any) index is used.

Do you have any custom indices on BKPF? A lot of them can confuse the database and make it hard to pick the right one.

Rob

[boolan]

What do You mean exactly with performance trace ?

I already have performed SQL Trace and explained the SQL.

It is explained exactly in the same way as the SQL in my program. But it does not make use of the index.

Do You mean runtime analysis ?

Yes, I have several custom indices on BKPF.

But even of these custom indices - my search program picks the right row in a flash.

[Former Member]

I notice that in your program, you are using hints, but I don't see them in the notes. Can that be why your program is using the correct index?

Rob

[boolan]

The same hint is bein used in the function module f4_get_result in my system. So it is not the reason.