topic Re: %_hints does not works in Application Development and Automation Discussions

%_hints does not works

Former Member — Fri, 07 Dec 2012 20:58:50 GMT

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

Re: %_hints does not works

ThomasZloch — Fri, 07 Dec 2012 21:03:46 GMT

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

Thomas

Re: %_hints does not works

Former Member — Fri, 07 Dec 2012 22:27:29 GMT

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

Re: %_hints does not works

volker_borowski2 — Sat, 08 Dec 2012 08:11:52 GMT

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

Re: %_hints does not works

former_member193464 — Sat, 08 Dec 2012 09:08:29 GMT

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 ...

Re: %_hints does not works

Former Member — Mon, 10 Dec 2012 12:03:50 GMT

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?

Re: %_hints does not works

Former Member — Mon, 10 Dec 2012 15:09:21 GMT

Hello

Thank you very much !

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

Mathieu