Problem with table-indexes when using select-options in select

[Former Member]

Hello experts,

is it right that table-indexes will not be used if you take select-options to select data from the database?

in detail:

i have build up an table-index for one of our db-tables and test it via an test-programm. The first test with '=' comparisons worked fine. Every key of the index was used; checked via ST05!

e.g.: SELECT * FROM TABLEA INTO ITAB WHERE keya = '1' AND keyb = '2' AND keyc = '3'.

Now i startet the test with select-options

e.g.: SELECT * FROM TABLEA INTO ITAB WHERE keya IN seltabA AND keyb IN seltabB AND keyc IN seltabC.

First of all i just filled the seltabs with only 1 value: eg: seltabA= SIGN = 'I' OPTION = 'EQ' LOW = '1' etc.

Everything worked fine. Every key of the index was used.

But now, I putted more than one entries in the seltabs e.g.

seltabA: SIGN = 'I' OPTION = 'EQ' LOW = '1'

SIGN = 'I' OPTION = 'EQ' LOW = '2'

SIGN = 'I' OPTION = 'EQ' LOW = '3'

From now on, the indexed was not used completely (with all keys).

Isn't that strange? How can i use select-options or sel-ranges with using the complete table-indexes?

Thanks a lot,

Marcel

[HermannGahm]

Hi Marcel,

>

> From now on, the indexed was not used completely (with all keys).
> Isn't that strange? 

Could you post the complete sql statement and execution plan, please?

We could give you more details then how your condition is mapped to the index

and how the query is executed.

Kind regards,

Hermann

[Former Member]

Hello Hermann,

thanks for your answer! Unfortunately these db-tables are custom-created one. I can't imagine that the sql-statement alone could help to analyze this problem:

here the select-statement:

SELECT * FROM /dbcond/01_cond AS dbcond

INTO CORRESPONDING FIELDS OF TABLE it_alv_frei

• WHERE lifnr = '0044100226'

• AND orgid = '00000040'

• AND koart_basis = '1200'

• AND status = '11'

• AND aenam IN r_aenam

WHERE lifnr IN r_lifnr

AND orgid IN r_orgs

AND koart_basis IN r_koart

AND status IN r_status

AND aenam IN r_aenam

AND gewaehr_kowid < 1

AND ( status = 00 OR status = 11 OR status = 12 ).

The inactive-variant worked fine (deactivated now with an *); the active one worked fine when there is just one entry in the select-options:

exmaple how to fill r_lifnr.

PERFORM check_lifnr.

SELECT DISTINCT lifnr FROM lfa1

INTO gl_lifnr

WHERE ktokk IN rs_lifnr.

IF NOT gl_lifnr IS INITIAL.

r_lifnr-low = gl_lifnr.

r_lifnr-sign = 'I'.

r_lifnr-option = 'EQ'.

APPEND r_lifnr TO r_lifnr.

ENDIF.

ENDSELECT.

But if i have more than one entries in one of the ranges, the table-index is not useg completely.

Thanks in advance,

Marcel

[HermannGahm]

Hi Marcel,

sorry i was not precise:

In order to analyze how the statement is executed:

one needs the sql statement as it is send to the database (from ST05) and the database specific execution plan,

Kind regards,

Hermann

[Former Member]

Hi Hermann,

i hope this helps:

this is the first one, which uses the complete index:

SELECT                                                                      
  "KOWID" , "LIFNR" , "KLPOS" , "ORGID" , "KOART" , "MATNR" , "GLTVON" ,    
  "GLTBIS" , "WERT" , "ABLIF" , "FAKIV" , "AENAM" , "AEDAT" , "AFORM" ,     
  "HERSTELLER" , "ARTGRP" , "OE_FREITXT" , "ARTFREITEXT" , "STATUS" ,       
  "TERDAT"                                                                  
FROM                                                                        
  "/dbcon/01_con"                                                        
WHERE                                                                       
  "MANDT" = ? AND "LIFNR" = ? AND "ORGID" = ? AND "KOART_BASIS" = ? AND     
  "STATUS" = ? AND "GEWAEHR_KOWID" < ? AND ( "STATUS" = ? OR "STATUS" = ? OR
  "STATUS" = ? )  WITH UR                  

RESULT: 5 IXSCAN /dbcon/01_con05 #key columns:  4 

And the second one, which does not use the complete index! The 3 ranges are filled each with 2 values. Remember; when i fill them each with only one value, the result is the same as you can see above(/dbcon/01_con05 #key columns: 4):

SELECT                                                                      
  "KOWID" , "LIFNR" , "KLPOS" , "ORGID" , "KOART" , "MATNR" , "GLTVON" ,    
  "GLTBIS" , "WERT" , "ABLIF" , "FAKIV" , "AENAM" , "AEDAT" , "AFORM" ,     
  "HERSTELLER" , "ARTGRP" , "OE_FREITXT" , "ARTFREITEXT" , "STATUS" ,       
  "TERDAT"                                                                  
FROM                                                                        
  "/dbcon/01_con"                                                        
WHERE                                                                       
  "MANDT" = ? AND "LIFNR" IN ( ? , ? ) AND "ORGID" IN ( ? , ? ) AND         
  "KOART_BASIS" IN ( ? , ? ) AND "GEWAEHR_KOWID" < ? AND ( "STATUS" = ? OR  
  "STATUS" = ? OR "STATUS" = ? )  WITH UR                                   

and here the access-plan

0 SELECT STATEMENT ( Estimated Costs = 5,139E+01 [timerons] )

5 1 RETURN

5 2 NLJOIN

5 3 [O] TBSCAN

5 4 SORT

5 TBSCAN GENROW

5 6 <i> FETCH /dbcon/01_con

7 IXSCAN /dbcon/01_con05 #key columns: 2

As you can see, only 2 keys were taken for indexed selection!

Any idea?

Kind regards,

MArcel

Edited by: Marcel Ebert on Jul 28, 2009 5:25 PM

[HermannGahm]

Hi,

i made it a little bit mor readable:

 SELECT ... FROM "/dbcon/01_con" WHERE "MANDT" = ? AND "LIFNR" = ? AND "ORGID" = ? AND "KOART_BASIS" = ? AND "STATUS" = ? AND "GEWAEHR_KOWID" < ? AND ( "STATUS" = ? OR "STATUS" = ? OR "STATUS" = ? ) WITH UR RESULT: 

5 IXSCAN /dbcon/01_con05 #key columns: 4 

 SELECT "... FROM "/dbcon/01_con" WHERE "MANDT" = ? AND "LIFNR" IN ( ? , ? ) AND "ORGID" IN ( ? , ? ) AND "KOART_BASIS" IN ( ? , ? ) AND "GEWAEHR_KOWID" < ? AND ( "STATUS" = ? OR "STATUS" = ? OR "STATUS" = ? ) WITH UR 

0 SELECT STATEMENT ( Estimated Costs = 5,139E+01 [timerons] ) 5 
1 RETURN 5 
2 NLJOIN 5 
3 [O] TBSCAN 5 
4 SORT 
5 TBSCAN GENROW 5 
6 <i> FETCH /dbcon/01_con 
7 IXSCAN /dbcon/01_con05 
#key columns: 2 

ok, this is DB2 for LUW (DB6). If oyu mark the IXSCAN and click the details button in the left top corner,

you can see the details for this step.

your index is MANDT, LIFNR, ORGID, KOART_BASIS? This 4 columns?

It looks like two of the inlists are used seargable only. Which doesn't necessarily mean a bad thing.

I don't know the "rules" when this happens.

If you compare the time per record needed in ST05 for both statements, is there a big difference?

Kind regards,

Hermann

[Former Member]

Hello Hermann,

sorry to be a bit late! Thanks alot your your very helpful answers! We decided to take it as it is!

After rebuilding the index we do not have any performance-problems anymore, although the index does not use all of the key-columns in some cases!

I will close now the thread!

Kind regards,

Marcel

[dflintstud1]

I have the same question as SAP documentation of the select statement says as a NOTE ..at the bottom. that relational operators that are not "=" will not be utilized for an index.. in other words any statement compare using = will be used for index selection until a non = is encountered and any subsquent conditions will be ignored as pertaining to index selection and utilization... SAP.. am i reading this correctly?