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

[Former Member]

Hi team,

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

Index BSAD_{1 has all three fields MANDT, BUKRS, AUGDT which are there in the where clause. But as per the execution plan index BSAD}0 is being used which is the primary one.

Also the stats for this table are are current.

Thanks in Advance.

Puneet.

[Former Member]

Hi team,

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

Index BSAD_{1 has all three fields MANDT, BUKRS, AUGDT which are there in the where clause. But as per the execution plan index BSAD}0 is being used which is the primary one.

Also the stats for this table are are current.

Thanks in Advance.

Puneet.

[Former Member]

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]

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

[andras_sebok]

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

[ThomasZloch]

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.

[UweFetzer_se38]

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

[Former Member]

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 vkp_{vkont = kkop}vkont ) INNER JOIN dfkkko AS kkko

ON kkop_{augbl = kkko}opbel

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.