Where clause with IN operator to use index?

[Former Member]

Hi,

If all the where fields are using IN in select statements will it uses the index to retrieve data from the table.

Example.

I have created index for fields BUKRS,BUDAT,RBSTAT,LIFNR and uses the following select.

SELECT BELNR, GJAHR, BLART, CPUDT, BUKRS, LIFNR, RMWWR, RBSTAT, ZUONR

FROM SAPR3.RBKP

WHERE BUKRS IN BUKRS

AND BUDAT IN BUDAT

AND RBSTAT IN RBSTAT

AND LIFNR IN LIFNR

AND BELNR IN BELNR

will it use the index. My basis person is saying this table uses full scan even after creating index on these fields, is it because of IN in where clause.

Regards,

Karthik.k

[seshatalpasai_madala]

Hi,

Its beacuse when you use IN, it converts it into OR statements so it will be like

SELECT BELNR, GJAHR, BLART, CPUDT, BUKRS, LIFNR, RMWWR, RBSTAT, ZUONR

FROM SAPR3.RBKP

WHERE BUKRS = "0001' OR BUKRS = '0002" etc

AND BUDAT IN BUDAT

AND RBSTAT IN RBSTAT

AND LIFNR IN LIFNR

AND BELNR IN BELNR.

Actual database does not know about IN of ABAP so it has to be converted to OR statements as per the SQL standards.

So its due to IN that INDEX is not being used.

For INDEX to be used you should have fields of the INDEX in the same order in the WHERE Clause.

Here you will have so many OR's that Database optimizer decides to use table scan instead.

Regards,

Sesh

[Former Member]

you can force using index

Select ERDAT WERKS MATNR
from LIPS 
into table Itab 
Where erdat in s_erdat 
and werks in s_werks
and Matnr in s_matnr
%_HINTS oracle ‘index(“ERDAT” “ERDAT~001”)’.

Note: here ERDAT~001 is ur index name.

[Former Member]

chandrasekar,

My index fields are BUKRS,BUDAT,RBSTAT and LIFNR. And i am using the same select statements. The fields are in right order.In this case how my SQL statement should be in order to use the index according to your tips.

Regards,

Karthik.k

[Former Member]

Anybody suggest?

[Former Member]

<b>if the index fields are used in the where cluase then the select query will select the data from the database table based on the where condition fields</b> ..

like example

in selection screen you data for the parameters are as follows .

<b>if bukrs is 1000 to 2000 .

budat is 12.01.2007 to 12.12.2007

then rstat is A to C

LIFNR IS 10000 to 20000 .</b>

then

selection of database from SAPR3 is based on then where condition ...

<b>it first pick only the bukrs 1000 to 2000 ... so most of the data is filtered .

then for budat 12.01.2007 to 12.12.2007 so most of the data will be filtered in the bukrs filtered itself .

then rstat A TO C so again filtered from the filtered budat field data .

then Lifnr 10000 to 20000 again filtered from the filtered rstat field data .

so it will be fast and thatiswhy we create index to filter the data based on some field index .</b>

but your <b>in</b> is option ... thatis range .... it doesn't do anything with index and all...

<b>for ranges ...

like

select-options : s_burks for bkpf-bukrs .

then

in where condition BUKRS IN S_BUKRS . // ***** so ->100000 to 20000

IF

Parameters : s_burks like bkpf-bukrs .

then

in where condition BUKRS = S_BUKRS // ***** so ->100000 .</b>

reward points if it is usefull ....

Girish

[Former Member]

hi,

Is there any way i could increase the performance of this SQL statement

SELECT BELNR, GJAHR, BLART, CPUDT, BUKRS, LIFNR, RMWWR, RBSTAT, ZUONR

FROM SAPR3.RBKP

WHERE BUKRS IN BUKRS

AND BUDAT IN BUDAT

AND RBSTAT IN RBSTAT

AND LIFNR IN LIFNR

AND BELNR IN BELNR

Is there any other operator other than IN available.

Regards,

Karthik.k