SQL Trace Explain shows incorrect where clause

[Former Member]

Hi,

I am facing a problem with a where clause wherein a where clause in the select statement is duplicated (as seen in the SQL explain plan) which is causing significant performance issues.

The select -->

SELECT *

INTO TABLE it_bsik

PACKAGE SIZE 1000

FROM bsik

FOR ALL ENTRIES IN t_bukrs

WHERE

lifnr EQ wa_test-vendor AND

bukrs EQ t_bukrs-bukrs_acc AND

bschl EQ '21'

%_HINTS ORACLE 'INDEX("BSIK" "BSIK~Y3")'.

The explain plan shows the below ->

SELECT

/*+

INDEX("BSIK" "BSIK~Y3")

*/

FROM

"BSIK"

WHERE

"MANDT" = :A0 AND "LIFNR" = :A1 AND "BUKRS" = :A2 AND "BSCHL" = :A3 OR "MANDT" = :A4 AND "LIFNR" =

:A5 AND "BUKRS" = :A6 AND "BSCHL" = :A7 OR "MANDT" = :A8 AND "LIFNR" = :A9 AND "BUKRS" = :A10

AND "BSCHL" = :A11 OR "MANDT" = :A12 AND "LIFNR" = :A13 AND "BUKRS" = :A14 AND "BSCHL" = :A15 OR

"MANDT" = :A16 AND "LIFNR" = :A17 AND "BUKRS" = :A18 AND "BSCHL" = :A19

Abov is quite confusing.

Any one having experienced this problem or details of a OSS note on this, please revert ASAP. I am checking table initial before for all entries and our Oracle DB is 9.2.0.8.0.

Thanks,

RK

[Former Member]

in ur select statement u can't use the below statement...

%_HINTS ORACLE 'INDEX("BSIK" "BSIK~Y3")'

[Former Member]

You can use the %_HINTS syntax in ABAP, but it should not be used. Let the parser of your sql statement choose the index. And the fields in your where clause is not using the primary index. Please re-arrange the fields in your where clause so that BUKRS comes first and later the LIFNR. That would improve the performancxe significantly.

Reward points if useful !!

~Ranganath

[Former Member]

HINST addition should not be the problem and the order of the fields does not seem to be the problem as well. I am confused as to why the explain returns multiple where conditions when I have only one. This increases the cost and hence the performance.

[Former Member]

In the SQL trace what you are seeing is the break up of the data from your internal table as you are using "for all entries" addition. The parser is generating the where clause dynamically depending on the entries in your internal table, which shall be used to convert to Native SQL.

[Former Member]

The answer you are seeing in the SQL explain is typical of a FOR ALL ENTRIES select statement.

FOR ALL ENTRIES is replaced by the application server by alternate SQL which can take one of two forms (depending on the SAP version and Parameter settings). Either it will replace the FOR ALL ENTRIES with an "field IN ( val1, val2, val3, ..., valn )" construct where n is determined by another SAP parameter, or it will replace the FOR ALL ENTRIES with "field = val1 OR field = val2 OR ... OR field = valn" which appears to be how your system is set. In this case, the value of n is limited to quite low values (around 10) for some database systems as the combination of AND's and OR's that end up in the WHERE block due to the FOR ALL ENTRIES multiplying the where logic can quickly cause the database optimiser to throw it's hands in the air and say "this is too hard" resulting in a table scan ignoring indexes / keys.

Some OSS notes that may be relevant are refered to by Peter Inotai in his response to <a href="/people/rob.burbank/blog/2007/03/19/joins-vs-for-all-entries--which-performs-better vs. FOR ALL ENTRIES - Which Performs Better?</a>

Note 48230 - Parameters for the SELECT ... FOR ALL ENTRIES statement

Note 652634 - FOR ALL ENTRIES performance with Microsoft SQL Server

Note 634263 - Selects with FOR ALL ENTRIES as of kernel 6.10

One possible way of improving your performance might be to declare a RANGES table for BSIK-BUKRS and populate it with the list of values in your t_bukrs, and then use "bukrs IN r_bukrs" in your where block. If the table t_bukrs is not too large, you can simply populate the range as "I" "EQ" entries singly - if not you may be able to use some "BT" ranges or even some "CP" patterns. The FOR ALL ENTRIES could then be removed.

Hope this is of some help

Andrew

[Former Member]

It looks like you have created an index (Y3) on BSIK which includes the client. Oracle HINTS shouldn't be used in a production environment. It's best to let the database optimizer pick the index. That's what it's there for. Sinxe index Y3 includes the client, you can try including that in the SELECT statement.

Rob