Index - Select Query

[sastry_gunturi]

Is there any way by which we can force the select statement to use index we want.........

[former_member387317]

Hi Karthik,

1. By default, if we do not specify anything,

2. The database, automatically,

picks up the optimum index,

based upon the sql,

and the WHERE conditions inside it.

3. Since R/3 is database independent,

its always better, not to specify

and index. Let the database decide the best route.

refer..

http://help.sap.com/saphelp_nw04s/helpdata/en/cf/21eb2d446011d189700000e8322d00/content.htm

You can force the sql statement to use a particular index by using <b>%_HINTS</b> 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.

<b><REMOVED BY MODERATOR></b>

Thanks & Regards

ilesh 24x7

Message was edited by:

Alvaro Tejada Galindo

.

[Former Member]

In Oracle, you can specify HINTS in the SELECT statement, but unless you are really certain, it's better to let the database make the decision.

Rob

[Former Member]

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.

You should ensure that fields in a where clause are always supported by an index, either a primary index or a secondary index. Secondary indexes are not available for cluster tables such as BSEG or pooled tables such as configuration tables & match code ids.

The columns belonging to a primary index have an 'X' under "Key" in the Dictionary: Table/Strucutre: Display Fields screen (SE11). They will always be the initial columns in a table.

Secondary indexes can be found from:

Dictionary: Table/Strucutre: Display Fields screen (SE11)->Indexes...Indexes for Table->choose an index and press enter- this will list the fields involved with a specific secondary index.

SE11 (Dictionary: Table/Strucutre: Display Fields screen)->Utilities->Database Utility->Extras->Database Object->Display will list all the fields and indexes for a table. Navigate to the bottom for the indexes.

xxxx_____0 is the primary unique index. xxxx______1, etc are the secondary indexes.

"To choose an index, the optimizer looks at the field names in the where clause and then it looks for an index having the same field names in the same order as they were specified in the where clause. Therefore, to ensure the system chooses the index you intend, specify the fields in the where clause in the same order as they appear in the index.... Commonly accesses SAP tables with their indexes.