topic Force the SQL statement to use custom index by using %_HINTS parameter. in Application Development and Automation Discussions

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

Former Member — Fri, 04 Mar 2011 15:55:39 GMT

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

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

ThomasZloch — Fri, 04 Mar 2011 16:12:10 GMT

"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

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

Sandeep_Panghal — Fri, 04 Mar 2011 16:17:26 GMT

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 .

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

Former Member — Mon, 07 Mar 2011 05:14:49 GMT

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

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

Former Member — Mon, 07 Mar 2011 08:31:37 GMT

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.