Force the SQL statement to use custom index by using %_HINTS parameter.

[Former Member]

I am writing the SQL statement to use the existing custom index (Z03) for table BSAD by using the %_HINTS parameter. No option to specify the fields in WHERE condition. If i am not using this secondary index, program is going to dump. Even the table also going to short dump. Whether it will impact on any other programs. <removed by moderator>.

SELECT specified fields

FROM bsad INTO table gt_bsad

WHERE bukrs = p_comp AND kunnr in s_kunnr

AND budat <= p_stdate

AND ZLSPR NE 'A'.

%_HINTS ORACLE 'INDEX("BSAD" "BSAD~Z03")'.

Edited by: Thomas Zloch on Mar 4, 2011 5:05 PM - no "needful" please

[ThomasZloch]

"No option to specify the fields in WHERE condition", what does this mean?

How is s_kunnr filled when this query runs?

How does your Z03 index look like?

"Even the table also going to short dump", what does this mean?

What dumps are you seeing, time outs?

A hint only affects this very statement where it is used, no other statements in the same or other programs.

Introducing or removing indexes can affect your entire system.

Thomas

[Sandeep_Panghal]

If you are getting dumps related to internal table memory overflow , you can try using CURSOR and linit the records by package size and process these records and FETCH the cursor again .

[Former Member]

Hi,

Using a specific index might improve/reduce the performance of your program, but not using it should not result in a dump, By dump do u mean time out error? and what about the WHERE condition? Please provide proper details

Regards,

Gopal

[Former Member]

hint question should not be discussed in this forum!

People who do not know how hints work (there is sufficient information in notes) should not use hints. I am quite sure that this the case here.