select statement : Secondary index

[Former Member]

Hi All,

I have created a secondary index for a table with 3 fileds and i need to change the existing select query which alreadys there, to add the secondary index , please can any oneprovide a select query how to write a select statement using secondary index.

urgent....

Thnsk in Advance

Raju

[Former Member]

nothing to worry about. Abap/SQL optimizer should choose right index.

BR< JAcek

[RichHeilman]

You can not explicitly say which index to use. It is derived by the db optimizer depending on the fields of your WHERE clause as well as the order of them. Change your WHERE clause to have the fields that you specified in your index as well as the same order. Then it is a good chance the index will be used.

Regards,

Rich Heilman

[Former Member]

you can force index by using HINTS. But hints are different for different Dbases.

BR< JAcek

[RichHeilman]

Hints are only for Oracle?

Regards,

Rich Heilman

[Former Member]

HI, Rich !!

No .

I use succesfuly for MAxDB. I sSuppose so that are ofor others dbases too.

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

B$R< JAcek

Message was edited by: Jacek Slowikowski

[Former Member]

Hi,

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

<u>For eg:</u>

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.

Thanks

Sudheer

[Former Member]

hi,

SPFLI is the DDIC table and its secondary index is SPFLI~001

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. 

Regards,

Sailaja.

[Former Member]

Hi Raju,

You can use the HINTS addition with select statement as other suggested, but it is not necessary to use it always.

The system will take care which one to use dynamically at run time.

If you want to know which index has been used in the select statement, you can go to ST05 transaction and start the trace and run your program. Stop the trace and use the explain button in the application toolbar for your select statement.

it would tel you which index has been used.

regards,

ravi

[Former Member]

Hi Raju,

what ever order u have given for the fields in the secondary index table just give in the simlar order in the where clause.

Thanks

Vikranth Khimavath