topic Re: using indexes. in Application Development and Automation Discussions

using indexes.

Former Member — Thu, 17 Sep 2009 08:44:02 GMT

Hi,

I have executed below statement

select vkorg

vkbur

vtweg

spart

into table it_vbak_tmp

from vbak

where vkorg eq p_vkorg

and vkbur in s_off

and vtweg in s_vtweg

and spart in s_spart.

I have following index on vbak table

NONUNIQUE Index VBAK~Z03

VKORG

VKBUR

VTWEG

SPART

when I am executing select statment above and simultaneously checking ST05, i find that index VBAK_{z03 is not at all followed. it still goes on index VBAK}Z01 (MANDT, VBELN).

what changes i need to do in above select statement so that index VBAK~z03 is followed on table VBAK ?

Please help

Regards,

Santosh

Re: using indexes.

Rui_Dantas — Thu, 17 Sep 2009 09:03:51 GMT

Are statistics for the index updated?

(you can check that also in ST05)

And yes, you are right, index Z03 should be used. In fact, even if you only had:

select  vkorg
         vkbur
         vtweg
         spart
 into table it_vbak_tmp
 from vbak.

... then that should be enough to use the index (in this case, an index full scan).

Re: using indexes.

Former Member — Thu, 17 Sep 2009 09:07:47 GMT

Hi Santosh,

That's strange system uses index Z01 rather than Z03 : have you checked if p_vkorg is filled? If not as there is less fields in Z01, it is normal that database optimizer uses this one since it should be smaller and thus faster...

But if you want to force the use of an index (but you should avoid that if possible!), you can use database hint like this :

SELECT vkorg vkbur vtweg spart
  INTO TABLE it_vbak_tmp
  FROM vbak
    WHERE vkorg EQ p_vkorg AND
          vkbur IN s_off AND
          vtweg IN s_vtweg AND
          spart IN s_spart
  %_HINTS ORACLE 'INDEX("VBAK", "VBAK~Z03")'. 
" Hint for Oracle but you can adapt for other databases

Best regards,

Samuel

Re: using indexes.

Former Member — Thu, 17 Sep 2009 09:24:30 GMT

Hi Santosh B,

Include/add MANDT field in your INDEX Z03..

In Open SQL if you are not specifying the MANDT then also Optimizer includes it by itself... That is why it's not using Z03

So Make the above change in your VBAK table index Z03

Hope it will solve your problem..

Thanks & Regards

ilesh 24x7

ilesh Nandaniya