query regarding ST05

[Former Member]

hi all,

i have a select query as

*****************************

SELECT * FROM KONH

INTO TABLE I_KONH

FOR ALL ENTRIES IN ITAB_KONH_TAB

WHERE

DATAB IN SO_DATAB AND

DATBI IN SO_DATBI AND

KNUMA_BO IN SO_BO AND

KNUMA_AG IN SO_AG AND

KNUMA_PI IN SO_PI AND

KSCHL IN SO_KSCHL AND

KVEWE = ITAB_KONH_TAB-KVEWE AND

KOTABNR = ITAB_KONH_TAB-KOTABNR.

********************

when i perform sql trace on this query, and when i select a query and push explain button, it shows the select query as

*******************

SELECT * FROM "KONH" WHERE "MANDT" = ? AND "DATAB" >= ? AND "DATBI" <= ?

AND "KVEWE" = ? AND "KOTABNR" = ?

UNION ALL

SELECT * FROM "KONH" WHERE

"MANDT" = ? AND "DATAB" >= ? AND "DATBI" <= ? AND "KVEWE" = ? AND "KOTABNR" =

?

UNION ALL

SELECT * FROM "KONH" WHERE "MANDT" = ? AND "DATAB" >= ? AND

"DATBI" <= ? AND "KVEWE" = ? AND "KOTABNR" = ?

UNION ALL

SELECT * FROM

"KONH" WHERE "MANDT" = ? AND "DATAB" >= ? AND "DATBI" <= ? AND "KVEWE" = ?

AND "KOTABNR" = ?

UNION ALL

SELECT * FROM "KONH" WHERE "MANDT" = ? AND

"DATAB" >= ? AND "DATBI" <= ? AND "KVEWE" = ? AND "KOTABNR" = ?

UNION ALL

SELECT * FROM "KONH" WHERE "MANDT" = ? AND "DATAB" >= ? AND "DATBI" <= ?

AND "KVEWE" = ? AND "KOTABNR" = ?

UNION ALL

SELECT * FROM "KONH" WHERE

"MANDT" = ? AND "DATAB" >= ? AND "DATBI" <= ? AND "KVEWE" = ? AND "KOTABNR" =

?

UNION ALL

SELECT * FROM "KONH" WHERE "MANDT" = ? AND "DATAB" >= ? AND

"DATBI" <= ? AND "KVEWE" = ? AND "KOTABNR" = ?

UNION ALL

SELECT * FROM

"KONH" WHERE "MANDT" = ? AND "DATAB" >= ? AND "DATBI" <= ? AND "KVEWE" = ?

AND "KOTABNR" = ?

UNION ALL

SELECT * FROM "KONH" WHERE "MANDT" = ? AND

"DATAB" >= ? AND "DATBI" <= ? AND "KVEWE" = ? AND "KOTABNR" = ?

*********************************

my question is

1. what happens to the rest of the fields in select query?

2. why the same query is repeated so many times?

3. if the where clause is broken in to different parts, is there any rule, which the clause is broken?

4. in st05, there was a column called operation, which had values, fetch, close, open..

out of which fetch was most expensive and i didnt had access to that source code!

please guide!

[Former Member]

I guess that you have read my blog on the SQL trace:

/people/siegfried.boes/blog/2007/09/05/the-sql-trace-st05-150-quick-and-easy

I know that it is a b it weak on the explain, because the explain is beyond the 'Quick and Easy' scope and must often be discussed with specific examples:

> 1. what happens to the rest of the fields in select query?

Your select has 'in' condition, if they do not appear in the explain, then they are probably not filled and therefore ignored.

> 2. why the same query is repeated so many times?

because of the FOR ALL ENTRIES, one fetch processes not the whole FOR ALL ENBTRIES table, and not one record, but some records (set be a parameter), here 10 records. Yoou should not try to change this parameter.

> 3. if the where clause is broken in to different parts, is there any rule, which the > clause is broken?

It is not broken in parts

4. in st05, there was a column called operation, which had values, fetch, close, open..out of which fetch was most expensive and i didnt had access to that source code!

this is the extended list. The OPEN should not appear in your trace, you should always run the program once before tracing to fill the buffer in order to get a defined state! In the trace of the repeated trace, there is no 'OPEN' but a 'REOPEN',, which is fast.

The 'REOPEN' gets the way how to access the table (index, union etc) from the cursor cache. And the FETCH is the actuall access to the database, thats the parts which costs.

The explain is not linked to the FETCH, only to the RE/OPEN, and PREPARE (that is the actual determination of the access path).

Access to the source code is also not linked to the FETCH.

Siegfried

[Former Member]

problem is in the select query.

fields in where clause shoud be in table order..

check this:

if not ITAB_KONH_TAB[] is not initial.

select *

from konh

into table i_konh

for all entries in itab_konh_tab

where KVEWE = ITAB_KONH_TAB-KVEWE and

KOTABNR = ITAB_KONH_TAB-KOTABNR and

DATAB IN SO_DATAB AND

DATBI IN SO_DATBI AND

KNUMA_PI IN SO_PI AND

KNUMA_AG IN SO_AG AND

KNUMA_BO IN SO_BO

endif.

perform SQL trace in ST05 for this query and check it.

if u get again same problem,

make sure that ur date fields , SO_DATAB , SO_DATBI should be of type parameter or select option??

when ever comparing, dates, the condition should be DATAB >= .... and DATBI <= .................