Problem with INDEX

[Former Member]

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,

[Former Member]

Hi,

1)

Make sure you have value in P_FKART

2)

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

<b>%_HINTS oracle 'INDEX("&TABLE&" "VBRK~Z01" "VBRK^Z01")'</b>

Thanks,

Naren

[Former Member]

HI,

You need to write the secondary indes like

SELECT * FROM spfli

<b>%_HINTS ORACLE 'INDEX("SPFLI" "SPFLI~001")'</b>

...

ENDSELECT.

<b>SPFLI~001 is the index</b>

[dairolozano]

This message was moderated.

[Rodrigo-Giner]

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

[sateeshkankanampati]

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

[former_member184158]

HI

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-dc1284e24...

Regards

Ibrahim

[kabil_g]

This message was moderated.