topic Re: select with IN in Application Development and Automation Discussions

select with IN

Former Member — Fri, 24 Aug 2007 09:42:09 GMT

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

Re: select with IN

Former Member — Fri, 24 Aug 2007 09:49:28 GMT

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 ( where vbeln in s_vbeln )

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

if it is useful

Reward Points

Regards

Vimal

Re: select with IN

paruchuri_nagesh — Fri, 24 Aug 2007 09:58:20 GMT

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

Re: select with IN

Former Member — Fri, 24 Aug 2007 09:58:35 GMT

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

Re: select with IN

varma_narayana — Fri, 24 Aug 2007 10:02:08 GMT

Hi Karthik..

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

Reward if Helpful

Re: select with IN

Former Member — Fri, 24 Aug 2007 10:05:18 GMT

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.

Re: select with IN

paruchuri_nagesh — Fri, 24 Aug 2007 10:09:08 GMT

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

Re: select with IN

Former Member — Fri, 24 Aug 2007 10:10:07 GMT

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.

Re: select with IN

varma_narayana — Fri, 24 Aug 2007 10:11:22 GMT

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.

Reward if Helpful

Re: select with IN

Former Member — Fri, 24 Aug 2007 10:11:35 GMT

Index is created on RBSTAT and LIFNR already.

Re: select with IN

Former Member — Fri, 24 Aug 2007 10:20:20 GMT

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

Re: select with IN

Former Member — Fri, 24 Aug 2007 10:24:00 GMT

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..........

Re: select with IN

Former Member — Fri, 24 Aug 2007 10:59:02 GMT

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