topic Re: coding indexes in select in Application Development and Automation Discussions

coding indexes in select

Former Member — Fri, 08 Apr 2005 07:07:04 GMT

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.

Re: coding indexes in select

Former Member — Fri, 08 Apr 2005 08:10:45 GMT

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

Re: coding indexes in select

ssimsekler — Fri, 08 Apr 2005 08:45:18 GMT

Hi Praveen

Inspect

Re: coding indexes in select

former_member328972 — Fri, 08 Apr 2005 23:02:06 GMT

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.

Re: coding indexes in select

Former Member — Mon, 11 Apr 2005 07:49:32 GMT

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.