Oracle hint needed for a select statement

[Former Member]

Hi,

The index of table was changed so I had readjust my SELECT statement . Now I needed to write a ORACLE hint for this new index .

How do write a hint ? Please advise . I have never written an hint before .

My select statement is as below and table is MDRS

SELECT * FROM MDRS

INTO TABLE ITAB_MDRS

FOR ALL ENTRIES IN ZMATERIAL

WHERE MATNR = ZMATERIAL-MATNR

AND XLOEK = X_XLOEK

AND KZEAR = X_KZEAR

AND WERKS IN WERKS_RANGE

AND BDART IN BDART_RANGE.

Thanks

Teresa

[Former Member]

hi,

check this for all entries ex , then u may understand the logic..

report ztest.

tables:pa0002.

data: begin of itab occurs 0,

pernr like pa0002-pernr,

vorna like pa0002-vorna,

nachn like pa0002-nachn,

end of itab.

data: begin of itab1 occurs 0,

pernr like pa0008-pernr,

begda like pa0008-begda,

stvor like pa0008-stvor,

ansal like pa0008-ansal,

end of itab1.

data :begin of itab2 occurs 0,

pernr like pa0021-pernr,

favor like pa0021-favor,

fanam like pa0021-fanam,

end of itab2.

data:begin of itab3 occurs 0,

pernr like pa0041-pernr,

dar01 like pa0041-dar01,

dat01 like pa0041-dat01,

end of itab3.

data:begin of final occurs 0,

pernr like pa0002-pernr,

vorna like pa0002-vorna,

nachn like pa0002-nachn,

begda like pa0008-begda,

stvor like pa0008-stvor,

ansal like pa0008-ansal,

favor like pa0021-favor,

fanam like pa0021-fanam,

dar01 like pa0041-dar01,

dat01 like pa0041-dat01,

end of final.

select-options:s_pernr for pa0002-pernr.

select pernr

vorna

nachn

from pa0002

into table itab

where pernr in s_pernr.

select pernr

begda

stvor

ansal

from pa0008

into table itab1

for all entries in itab

where pernr = itab-pernr.

select pernr

favor

fanam

from pa0021

into table itab2

for all entries in itab1

where pernr = itab1-pernr.

select pernr

dar01

dat01

from pa0041

into table itab3

for all entries in itab2

where pernr = itab2-pernr.

loop at itab.

final-pernr = itab-pernr.

final-vorna = itab-vorna.

final-nachn = itab-nachn.

read table itab1 with key pernr = itab-pernr.

final-begda = itab1-begda.

final-stvor = itab1-stvor.

final-ansal = itab1-ansal.

read table itab2 with key pernr = itab1-pernr.

final-favor = itab2-favor.

final-fanam = itab2-fanam.

read table itab3 with key pernr = itab2-pernr.

final-dar01 = itab3-dar01 .

final-dat01 = itab3-dat01.

append final.

clear final.

endloop.

loop at final.

write:final-pernr ,

final-vorna ,

final-nachn ,

final-begda ,

final-stvor ,

final-ansal ,

final-favor ,

final-fanam ,

final-dar01 ,

final-dat01 .

endloop.

regards,

venkat.

[Former Member]

What is the index look like now? Are you sure that it isn't already using it? If your WHERE clause matches (more or less), the index, the optimizer may decide on its own to use it.

[Former Member]

The order of the index is

:

MANDT

MATNR

XLOEK

KZEAR

WERKS

BDART

[Former Member]

Your where clause matches the index perfectly. I do not think you need a hint. I would verify this by running a SQL trace (txn ST05). That will tell you the Explain Plan.

You may want to also tell your basis guys to make sure that there are "statistics" on the table and index, just to be safe.

[Former Member]

Sample code:

select .... from table1 into table itab
         where field1 = value1
          and  field2 = value2
        %_hints oracle 'INDEX (TABLE1 INDEX_NAME)'.

Get the index name from SE11 --> Indexes --> double click on index number --> Index name as stored in database system.

[Former Member]

What do you mean "the index was changed?"

What does RESB-M look like now?

Rob

[marcelom_bovo]

Is it needed to put %_hints when we want to use a index in a select statement?

tks

[Subhankar]

Oracle hints sample code is below.

SELECT *

FROM ABCD

INTO TABLE t_ABCD

WHERE f1 = xyz

%_HINTS ORACLE 'INDEX("ABCD" "ABCD~Z01")'.

As Jerry suggested index will found automatically. Just change you where clause as below. Just do a performance analysis from st05 or SM50, if any index is found or not. If you changed the table index or did any changes in the table just adjust the table indexes from se14.

SELECT * FROM MDRS

INTO TABLE ITAB_MDRS

FOR ALL ENTRIES IN ZMATERIAL

WHERE MATNR = ZMATERIAL-MATNR

AND WERKS IN WERKS_RANGE

AND XLOEK = X_XLOEK

AND KZEAR = X_KZEAR

AND BDART IN BDART_RANGE.

Thanks

Subhankar

[Former Member]

thanks a lot problm solv

[Former Member]

Appears that MDRS is a view for table RESB, which has an 'M' index that should do most of what you need....

I'd try....

if lines( zmaterial ) gt 0.
SELECT * FROM RESB INTO TABLE ITAB_resb
FOR ALL ENTRIES IN ZMATERIAL
WHERE MATNR   =  ZMATERIAL-MATNR
     AND WERKS  IN  WERKS_RANGE
     AND XLOEK   =   X_XLOEK
     AND KZEAR   =   X_KZEAR
     AND BDART IN BDART_RANGE.
endif.

Check your tract for that statement and see if you do an index range scan for the "m" index on RESB.

Edited by: DaveL on Aug 31, 2011 2:05 PM