topic Re: Index in Application Development and Automation Discussions

Index

Former Member — Fri, 06 Oct 2006 06:09:47 GMT

Hi,

I have a small doubt.

A select statement is queried on 5 fields

Eg :

select a b c

from table1

where field1 eq p

field2 eq q

field3 eq r

field4 eq s

field5 eq t.

Suppose there is an index created for the table on field2 and field3.

Already the query is running with index on field2 and field3. If I create another index with all the fields in the where clause, will there be considerable perfomance improvement?

Thanks in advance.

Vishnu Priya

Message was edited by: Vishnu priya

Re: Index

Former Member — Fri, 06 Oct 2006 06:12:14 GMT

Hi,

The where condition in the select statement should have all the fields defined in the index in the same sequence.Ofcourse queries which hit index are faster than the queries which do not hit the index.

You can force the database to pick the index by using database Hints.

Hope this helps.

Re: Index

Former Member — Fri, 06 Oct 2006 06:13:31 GMT

just refer amit answer ur point will clear...

Re: Index

Former Member — Fri, 06 Oct 2006 06:32:54 GMT

Hi,

The primary index contains the key fields of the table and a pointer to the non-key fields of the table. The primary index is created automatically when the table is created in the database.

If you have the Index with your Where clause fields, you can use that to improve your Select statmnet performence ..

You can force the sql statement to use a particular index by using %_HINTS parameter.

For eg:

SELECT carrid connid cityfrom

FROM spfli INTO (xcarrid, xconnid, xcityfrom)

WHERE carrid = 'LH ' AND cityfrom = 'FRANKFURT'

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'.

WRITE: / xcarrid, xconnid, xcityfrom.

ENDSELECT.

Refer to the SAP Note #129385 and related notes for further information

Regards

Sudheer