topic Index in Application Development and Automation Discussions

Index

kiran_k8 — Mon, 04 Feb 2008 05:48:52 GMT

Hi,

Is it mandatory to use all the fields of an index in the where clause of a select statement in terms of performance.

Thanks,

K.Kiran.

Re: Index

former_member156446 — Mon, 04 Feb 2008 05:50:51 GMT

its not mandatory that you need to use all.. use what all you wanted.. but what ever you choose its better to be a primary key field.

Re: Index

Former Member — Mon, 04 Feb 2008 05:51:28 GMT

Hi,

YES it is Good to use all the fields of an index in the where clause of a select statement in terms of performance.

Better to pass...

Specially in case of SELECT single...

REward if helpful..

GAURAV J.

Edited by: GAURAV on Feb 4, 2008 6:51 AM

Re: Index

Former Member — Mon, 04 Feb 2008 06:01:48 GMT

Check for Equality and Link Using AND The database index search is particularly efficient if you check all index fields for equality (= or EQ) and link the expressions using AND.

Re: Index

Former Member — Mon, 04 Feb 2008 06:03:31 GMT

It is always good to use index for the selction criteria of your select statement, however you need not use all the index.

Re: Index

kiran_k8 — Mon, 04 Feb 2008 06:34:07 GMT

Hi,

Getting a mixed response.Looking forward to more replies.

K.Kiran.

Re: Index

kiran_k8 — Mon, 04 Feb 2008 09:45:31 GMT

select b~mblnr

b~menge

b~lfbnr

b~lfpos

from mseg as b

inner join mkpf as a

on b_{mblnr = a}mblnr

into corresponding fields of table it_rjct

where b~werks = p_werks and

a~budat between it_cperiod-begda and it_cperiod- endda and

b~bwart = '122' and

b~ebeln NE SPACE.

In MSEG werks and bwart are in secondary index.

In MKPF budat is in secondary index.

Does the above select statement satisifies the following conditions in terms of usage of indexes.How to fine tune it.

Check for Equality and Link Using AND

The database index search is particularly efficient if you check all index fields for equality (= or EQ) and link the expressions using AND.

Use Positive Conditions

The database system only supports queries that describe the result in positive terms, for example, EQ or LIKE. It does not support negative expressions like NE or NOT LIKE.

If possible, avoid using the NOT operator in the WHERE clause, because it is not supported by database indexes; invert the logical expression instead.

Thanks,

K.Kiran.

Edited by: Kiran K on Feb 4, 2008 10:49 AM

Re: Index

Former Member — Mon, 04 Feb 2008 09:57:05 GMT

Hi,

ITS not mandatory

But to raise performance you should try to pass it..

REGARDS,

GAURAV J.