select with IN

[Former Member]

Hi,

If I use IN in select statement will it use index. For me all my where conditions are using IN will the table use index to search.

Regards,

Karthik.k

[Former Member]

Hi Karthik,

The IN operator is used in where clause to compare range of values.

eg. if u used

select-options : s_vbeln for vbak-vbeln.

in where clause u have to specify as (<b> where vbeln in s_vbeln</b> )

so it compares from low value to high value for ur query.

if it is useful

<b>Reward Points</b>

Regards

Vimal

[paruchuri_nagesh]

in operator is to select range values when we are using select-options in selection screen

indexes can be assigned by sytem automatically user has nothing to do, no need to assign those explicitly.

but indexes improve performance of select statement while fetching the data

reward for useful answers

regards

Nagesh.Paruchuri

[Former Member]

OK.But will it use index to search the table.

[varma_narayana]

Hi Karthik..

It will use INDEX when we give IN. Index will not be used in we give NE or NOT operator.

<b>Reward if Helpful</b>

[Former Member]

Hi Nages and vimal ur right but if i use only IN in select statement will it going to improve the fetch on database level.I have a select statement

SELECT BELNR GJAHR BLART CPUDT BUKRS LIFNR

INTO TABLE I_PARKED_LIV

FROM RBKP

WHERE RBSTAT IN R_RBSTAT

AND LIFNR IN S_LIFNR

AND BUKRS IN S_BUKRS

AND CPUDT IN S_CPUDT

AND BLART IN S_BLART

AND BELNR IN S_BELNR

AND BUDAT IN S_BUDAT

Is this statement going to use index 4 for this table.

[paruchuri_nagesh]

hi karthik if ur using primary key field in where condition system uses primary index

if it is non primary key field user has to create secondary index to support where clause then only it will fetch data otherwise it wont

check with simple program

reward if u find use ful

regards

Nagesh.Paruchuri

[varma_narayana]

Hii..

The best way to find whether a query using any index is to Perform SQL TRACE.

Tcode ST05.

There you can Go to the Trace list and Click on EXPLAIN button to See whether the Query is executed using an INDEX.

<b>Reward if Helpful</b>

[Former Member]

Hi varama but still my basis person is saying it using full table scan for the above select statement even index exists because of IN operator. Is it true.

He is saying database performance operator will not consider IN to select data using Index.

[Former Member]

Index is created on RBSTAT and LIFNR already.

[Former Member]

Hi,

Since BELNR and GJAHR are the key fields in table RBKP and the index you have created on is on a non primary key field the index search on this table would not work.

Regards,

Santosh Nayak

[Former Member]

hi

IN operator are mainly used for comparing the selection screen values with database values like

selection-screen : begin of block blk1 with farme title text-001.

select-options : s_erdat for mara-erdat.

parameters : p_matnr like mara-matnr.

selection-screen : end of block blk1.

select single * from mara into table itab_mara where matnr = p_matnr and

erdat IN s_erdat.

reward if useful..........

[Former Member]

Hi Karthik,

'IN' is always a performance killer be it in ABAP or DB SQL.

Whenever u use IN the query skips the index.

If u debug you would come to know that all the 'IN' clauses of the ABAP are converted into 'OR' statements.

So, the answer is NO it doesn't use index