topic Index usage in Application Development and Automation Discussions

Index usage

Former Member — Tue, 09 Mar 2010 14:28:58 GMT

Dear Experts,

I have an index already created on LIKP on 2 fields

VKORG and WADAT_IST.

But my selection criteria on the table has 3 fields

VKORG, LFART and WADAT_IST.

So, does the select stmt uses the index.

For various reasons, we cannot change the existing index and also create a new one.

If it does not use, is there a way that we can force the system to use that index, if so any disadvantages?

Please suggest

Thanks

-Amit

Re: Index usage

PedroGuarita — Tue, 09 Mar 2010 14:37:34 GMT

Yes there is a way you can point to an index, use %_hints in the select instruction.

Example :


    SELECT bukrs gjahr belnr blart budat usnam tcode
      awtyp bldat cpudt INTO TABLE t_bkpf FROM bkpf WHERE
      bukrs = s_bukrs-low AND
      budat BETWEEN s_budat-low and s_budat-high
*      budat in s_budat
      %_hints ORACLE 'BKPF~2'.

Also, you can use only two fields in the selection criteria and then delete from the internal table where it does not match the 3rd field criteria, sometimes it's faster that way, depending on the volume of data.

Re: Index usage

kesavadas_thekkillath — Tue, 09 Mar 2010 14:46:12 GMT

Run the trace in st05, check the Filter predicates and access predicates.

You can also force the index by using Oracle hints.

Please search in forum for more details.

Re: Index usage

Former Member — Tue, 09 Mar 2010 14:50:44 GMT

One usually codes the where clause to list the conditions in the same order as the index field list. The fact that you have another field would indicate that the index table itself cannot be the only determining factor in whether a row will be retained, and that will cause a small performance degradation.

If I were doing this, I would write the where clause in index field sequence (vkorg, wadat_ist, then lfart), execute with ST05 on, and review/explain the open/reopen on LIKP. You may find that the index is used, or you may find that the database optimizer chooses another index. If another index, review that list...it may actually be a better choice (we usually trust the db optimizer to make the right choice, particularly if runstats are kept up to date).

Re: Index usage

HermannGahm — Tue, 09 Mar 2010 14:57:48 GMT

Hi,


> I have an index already created on LIKP on 2 fields 
> VKORG and WADAT_IST.
> 
> But my selection criteria on the table has 3 fields
> 
> VKORG, LFART and WADAT_IST.
> 
> So, does the select stmt uses the index.
>

the optimizer SHOULD use it. But you'd better doublecheck with ST05 / ST04.

Kind regards,

Hermann