topic Re: Index in Application Development and Automation Discussions

Index

Former Member — Tue, 29 Apr 2008 09:07:25 GMT

Hi,

There is an secondary index in BKPF with fields in the order BUKRS GJAHR AWKEY STBLG STJAH.

My Select statement is :

select bukrs belnr gjahr awkey xblnr blart from bkpf

into table itab_accdoc for all entries in kaep_covp_ext1

where bukrs eq kaep_covp_ext1-bukrs and

gjahr eq kaep_covp_ext1-gjahr and

awkey eq kaep_covp_ext1-refkey.

Through ST05, I found the select statement did not go through the above mentioned index but went through another index with fields MANDT BUKRS.

Kindly suggest ways to make the Select statement go through the correct Index so that the time reduces for selection.

Thanks in advance.

Re: Index

Former Member — Tue, 29 Apr 2008 09:10:33 GMT

hi,

the index is picked by the optimizer during runtime.u can explicitly specify the index using ORACLE HINTS.

ex:

SELECT * UP TO 10 ROWS FROM csks

WHERE kokrs <> space AND

kostl <> space

%_HINTS ORACLE 'index(csks"J")'.

WRITE: / csks.

ENDSELECT.

regards,

madhu

Re: Index

Former Member — Tue, 29 Apr 2008 09:15:28 GMT

hi,

Check out the below sample code and explicitly call the index ...


DATA: dbcur TYPE cursor.

*--Selecting data from purchase doccument item data.
OPEN CURSOR dbcur FOR
SELECT ebeln
ebelp
loekz
aedat
txz01
matnr
werks
matkl
idnlf
ktmng
menge
meins
netpr
peinh
elikz
erekz
wepos
weunb
repos
webre
kzabs
lmein
mtart
retpo
bsgru FROM ekpo
WHERE aedat IN s_aedat
%_HINTS ORACLE 'INDEX("EKPO""Z02")'.
DO.
FETCH NEXT CURSOR dbcur APPENDING TABLE it_ekpo PACKAGE SIZE 20000.
IF sy-subrc <> 0.
CLOSE CURSOR dbcur.
EXIT.
ENDIF.
ENDDO.

Re: Index

Former Member — Tue, 29 Apr 2008 10:10:17 GMT

Though I wrote

%_HINTS ORACLE 'INDEX ("BKPF""ZAW")'

Still the Query did not go through the Index. What can be done.

Kindly can u also suggest a method to write in Open SQL..

Edited by: Rajpriya Paulraj on Apr 29, 2008 3:00 PM

Re: Index

ThomasZloch — Tue, 29 Apr 2008 11:10:24 GMT

the correct syntax for the hint is:

%_HINTS ORACLE 'INDEX("BKPF" "BKPF~ZAW")'

Is the index activated and does it exist on the database?

It might also be a good idea to rebuild the table statistics for BKPF via DB20, then you might not need the hint at all.

Greetings

Thomas