Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

how to insert ORACLE HINT into select statement....

Former Member
0 Kudos
3,854

Hi team,

Can you please suggest me how to add an Oracle hint to point the Program to BSAD~1.

Index BSAD1 has all three fields MANDT, BUKRS, AUGDT which are there in the where clause. But as per the execution plan index BSAD0 is being used which is the primary one.

Also the stats for this table are are current.

Thanks in Advance.

Puneet.

1 ACCEPTED SOLUTION

Former Member
0 Kudos
155

Hi punnet, look at this example:

SELECT MAX( kkop~augbd )

INTO it_gen_docs-augbd

FROM ( fkkvkp AS vkp INNER JOIN dfkkop AS kkop

ON vkpvkont = kkopvkont ) INNER JOIN dfkkko AS kkko

ON kkopaugbl = kkkoopbel

WHERE vkp~exvko = ti_cuentas-exvko

AND kkop~bukrs = p_bukrs

AND kkop~blart IN r_blart

AND kkop~augst = '9'

AND kkko~blart IN r_blartd

%_HINTS ORACLE 'INDEX("DFKKOP" "DFKKOP~Z07")'.

DFKKOP is the table name and DFKKOP~Z07 is the index.

I hope this helps you.

6 REPLIES 6

Former Member
0 Kudos
155

BSAD is a secondary index table for customers. If you don't know the customer number, it's going to be pretty difficult to use this table.

Rob

ThomasZloch
Active Contributor
0 Kudos
155

Adding hints and so overriding the optimizers decision should be the absolute exception. Please post your select statement for BSAD here, especially the where-conditions, maybe there is an explanation why the primary key is being used instead of index 1.

Thomas

0 Kudos
155

Optimizers need to be overridden all the time, that is the whole purpose of hints.

0 Kudos
155

wow, more than ten years later…

This statement seems to come from a DB admin rather than an ABAP developer (the original post's context in 2008).

From a developer point of view, I do not agree with you, or I do not understand the 2019 context.

Please explain.

0 Kudos
155

Interesting statement from an SAP employee. Seems I've done something wrong the last 30 years...

Former Member
0 Kudos
156

Hi punnet, look at this example:

SELECT MAX( kkop~augbd )

INTO it_gen_docs-augbd

FROM ( fkkvkp AS vkp INNER JOIN dfkkop AS kkop

ON vkpvkont = kkopvkont ) INNER JOIN dfkkko AS kkko

ON kkopaugbl = kkkoopbel

WHERE vkp~exvko = ti_cuentas-exvko

AND kkop~bukrs = p_bukrs

AND kkop~blart IN r_blart

AND kkop~augst = '9'

AND kkko~blart IN r_blartd

%_HINTS ORACLE 'INDEX("DFKKOP" "DFKKOP~Z07")'.

DFKKOP is the table name and DFKKOP~Z07 is the index.

I hope this helps you.