topic Re: Problem with INDEX in Application Development and Automation Discussions

Problem with INDEX

Former Member — Mon, 12 Feb 2007 16:26:16 GMT

When the following SELECT SENTENCE is executed:

SELECT DISTINCT bukrs vbeln kunag fkart waerk fkdat

FROM vbrk CLIENT SPECIFIED

INTO TABLE ti_factura

WHERE mandt = '400' and fkart IN p_fkart

AND fkdat between p_budat1 and p_budat2

it alway performs a sequential search dispite we've created and activated an index with these fields:

MANDT

FKART

FKDAT

What can I do to get the SELECT use the index ?

Regards,

Re: Problem with INDEX

Former Member — Mon, 12 Feb 2007 16:31:18 GMT

Hi,

Make sure you have value in P_FKART

To force index to a sql use the following..

Example if you have created index name as Z01...Changes marked in bold..

SELECT DISTINCT bukrs vbeln kunag fkart waerk fkdat

FROM vbrk CLIENT SPECIFIED

INTO TABLE ti_factura

WHERE mandt = '400' and fkart IN p_fkart

AND fkdat between p_budat1 and p_budat2

%_HINTS oracle 'INDEX("&TABLE&" "VBRK~Z01" "VBRK^Z01")'

Thanks,

Naren

Re: Problem with INDEX

Former Member — Mon, 12 Feb 2007 17:17:45 GMT

HI,

You need to write the secondary indes like

SELECT * FROM spfli

%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'

...

ENDSELECT.

SPFLI~001 is the index

Re: Problem with INDEX

dairolozano — Sat, 27 Jun 2015 04:44:04 GMT

This message was moderated.

Re: Problem with INDEX

Rodrigo-Giner — Sat, 27 Jun 2015 15:27:11 GMT

Pls check that the index really exist in the DB.

In the SE11 when you go the the indexes choose it and make sure that the index exist in the DB

"Index VBRK~ZFA exists in database system ORACLE"

The index being activated is not the same that exists in the DB. You can have an index activated that says that it doesn't exist in the DB.

So make sure it rlly exist.

Regards

Re: Problem with INDEX

sateeshkankanampati — Sat, 27 Jun 2015 17:26:30 GMT

Hello Leonardo,

1) If you want to hit your index on SELECT statement exactly, specify index name like %_HINTS ORACLE 'INDEX("KNA1" "KNA1~001")'.

SELECT KUNNR

FROM KNA1

INTO TABLE lt_kunnr

%_HINTS ORACLE 'INDEX("KNA1" "KNA1~001")'..

2) Even your index is not hit on your select exactly,ask your basis consultants run DBSTAT on table. It adjust index and table.

Thanks,Sateesh K

Re: Problem with INDEX

former_member184158 — Sun, 28 Jun 2015 00:04:19 GMT

You can use an index with the statement

%_HINTS oracle 'INDEX("<index name>")'

example:

SELECT DISTINCT bukrs vbeln kunag fkart waerk fkdat

FROM vbrk CLIENT SPECIFIED

INTO TABLE ti_factura

WHERE mandt = '400' and fkart IN p_fkart

AND fkdat between p_budat1 and p_budat2

%_HINTS oracle 'INDEX("Z01")'.

look to this document, may help you.

Performance Improvement using Indexes and ABAP Hints

http://www.sdn.sap.com/irj/scn/go/portal/prtroot/docs/library/uuid/50509a26-8186-2d10-bca9-dc1284e24b78?overridelayout=t…

Regards

Ibrahim

Re: Problem with INDEX

kabil_g — Sun, 28 Jun 2015 09:38:00 GMT

This message was moderated.