coding indexes in select

[Former Member]

hai all,

can any body suggest me how to use the table index in the select statement. any possible suggestion is welcome.

thanx a lot in advance.

kittu.

[Former Member]

Hi Praveen,

Using Open SQL (SAP's SQL abstraction layer) there is no real way to explicitly specify which indexes are used by the select (and other SQL) statements.

You have to rely on the database for this.

Prior to Oracle version 8 (if you are running Oracle) you had rules based query optimisation. This means that you could encourage the database to use indexes by using all the fields of the index in the same order as in the index in the WHERE clause. For example:

Custom secondary index on VBAP: MANDT, MATNR, NETWR

Your select statement to use this index could look like (the client field MANDT is implied):

SELECT *

FROM VBAP

WHERE MATNR = 'XXXXX'

AND NETWR = '1000'.

ENDSELECT.

With Oracle version 8 and beyond, it has changed to statistical based optimisation, which means that the database chooses which indexes to use (if any) based on statistical information it has for each database object (importantly, these statistics must be kept up to date for it to work effectively). So the database is likely to choose the same index for vbap even if you change your query to:

SELECT *

FROM VBAP

WHERE NETWR = '1000'

AND MATNR = 'XXXXX'.

ENDSELECT.

If you really feel then need to force an index to be used (and the database is not does not want to choose the one you wish), then you may have to resort to the EXEC SQL, ENDEXEC statements to allow you to code native database SQL. With Oracle you can use database 'HINTS' in your queries, but I am not sure of their syntax.

If you are not using Oracle then you may want to look at the documentation for your database.

Hope that helps.

Cheers,

Brad

[ssimsekler]

Hi Praveen

Inspect

[former_member328972]

I've used the hints command successfully, though as the included thread says, it is database specific in the sense that the hint would not be applied in another database. The SQL would still work though I suspect just the hint would be ignored.

This was in a very specific case where we knew the value to be selected for was always going to be in a very specific range but the database optimizer would not know that (in fact it was the cpu date on bkpf). It made an absolutely massive improvement to performance.

[Former Member]

For using indexes in the select statement, include the following code after the where clause in the statement.

%_HINTS ORACLE

'INDEX("&TABLE&" "XYZ~ABC")'.

where,

XYZ = database table

ABC = index.

However, the index has to be created for a field used in the where clause.

Regards,

Kumod.