Select using Secondary Index

[PrasadRaghavan]

Dear friends

I have created a secondary index on a table.

When I do a select, I want the DB to use that index specifically. Is it possible to specify the secondary index usage in ABAP when we query the table ? as in

select * from <tabname> where f1 = val1 and f2 = val2.

tabname has a secondary unique index.

If yes, how ?

Are there any limitations that the usage of the secondary unique index usage is dependent on the DB ?

Thanks for your quick reply.

Regards

Sundar

[PrasadRaghavan]

Dear friends

I have created a secondary index on a table.

When I do a select, I want the DB to use that index specifically. Is it possible to specify the secondary index usage in ABAP when we query the table ? as in

select * from <tabname> where f1 = val1 and f2 = val2.

tabname has a secondary unique index.

If yes, how ?

Are there any limitations that the usage of the secondary unique index usage is dependent on the DB ?

Thanks for your quick reply.

Regards

Sundar

[Former Member]

Hello Prasad,

So long as you specify all the fields defined in the index in the same order in your select statement and with EQ as the relational operator, the secondary index will be used.

I don't see why you wish to specify it explicitly.

Regards,

Anand Mandalika.

[ssimsekler]

Hi Prasad

It's a task of RDBMS to select which index to use and like Anand, I do not a way to explicitly define the index RDBMS will use. After calculations and optimizations, RDBMS decides the index to use.

Regards

*--Serdar <a href="https://www.sdn.sap.com:443http://www.sdn.sap.comhttp://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/com.sap.sdn.businesscard.sdnbusinesscard?u=qbk%2bsag%2bjiw%3d">[ BC ]</a>

[Former Member]

Hello Prasad,

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.

Regards

Sharan

[Former Member]

Hello Sharan / Prasad,

I had known about the %_HINTS option, but did not want to mention it here. The option, I gather, is for use in only very specialized scenarios.

Consider the following points, before you use this option:

1.<i> The %_HINTS option(s) are always specific to just one release of just one database system.</i> You cannot trust your program to perform as expected if the database were to be different.

2. With the introduction of a <i>cost-based optimizer</i> for most of the newer database servers, beyond a certain point the programmer does not have to worry as much about database access part, as he should about the application logic.

3. I seriously doubt that the syntax Sharan had provided would <i>force</i> the database to use the particular index.

Even if we were to assume that it would, it is extremely disadvantageous for us to do so in case we are not really specifying the fields defined in the index in the same order and with appropriate relational operators.

And so long as you specify the fields correctly in the SELECT statement, the index would be used anyways, without the %_HINTS option !! )

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

Regards,

Anand Mandalika.