%_hints does not works

[Former Member]

Hi guys,

Could you help to debug this instruction and explain why index DFKKOP~4 is not used :

SELECT a~vkont a~opbel a~opupw a~opupk a~opupz a~augst a~hvorg a~tvorg a~waers a~faedn a~betrw a~xblnr a~vtref a~applk a~bldat a~blart a~stakz a~xanza a~whgrp a~abwtp a~augrs a~grkey b~cpudt b~cputm     

INTO TABLE it_dfkkop

FROM dfkkop AS a INNER JOIN dfkkko AS b ON a~opbel = b~opbel FOR ALL ENTRIES IN f_it_vkont

     WHERE vkont EQ f_it_vkont-vkont AND augst EQ space

%_hints oracle 'index ("DFKKOP","DFKKOP~4")'. 

There is DFKKOP~Z01 which is used (in trace st04)

Index DFKKOP~4 is :

MANDT

AUGST

VKONT

BUKRS

AUGDT

Index DFKKOP~Z01 is :

MANDT

VKONT

AUGST

Many thanks for your help...

Mathieu

[ThomasZloch]

Hmm, try replacing the comma between table and index name with space. If this does not work, we'll have to look further.

Thomas

[Former Member]

Hello,

We try with space but it doesn't works.

Moreover, with a unique selection (without inner join) HINTS instruction works correctly : Index DFKKOP~4 is used.

Thanks for your help.

Mathieu

[volker_borowski2]

Hello,

no, in your second case the hint does not work, merly the optimizer just accidently works as you expect 🙂

no, in a select without a join, no table-alias is used in the statement, and the tablename will work.

In a join, the tablenames get aliases in the statement sent to the DB. When a table gets aliases in the statement, I had it very frequently, that the hint is not taken when using the table name.

You need to specify the alias, which will be i.e. T_00 or T_01. Might be T1 or T2 as well.

You need to find out in the SQL trace to see what will be parsed.

It might even be that ABAP is using your aliases ("a" and "b"), but you need to find out.

So check what alias the DFKKOP table is getting in the real execution and adjust the hint:

%_hints oracle 'index (T_00 "DFKKOP~4")'

Hope this will work for you as well.

But: did you verify that index Z01 is really slower? As far as the fields used in WHERE concerned, Z01 should perform better because it should be physically smaller. Since both fields are EQ specified, both indexes should work well, but Z01 should have a better re-use ratio for already read blocks.

Volker

Message was edited by: Volker Borowski Adjusted, after re-read: compared plan was without join

[former_member193464]

the order in which you have defined secondary index in 04 is augst and vkont where as in where query the order is different ..... can you check by reversing the order in where query ...

[Former Member]

your query is pretty complex using join with for all entries.

Since you are using secondary index, i am guessing performance is poor.

I would suggest to split the join into a 'For all entries(FAE)'.

FAE is generally faster compared to join.

You have to use FAE on both the tables.

Can you give the number of records which are being fetched by your query?

[Former Member]

Hello

Thank you very much !

With %_HINTS ORACLE 'index (T_00, "DFKKOP~4")' it works !!

Mathieu