Secondary Index Picked with one field short in select query

Hi,

We have a select query as follows

select  single lgort       vgbel        vgpos
    into   (lips-lgort,lips-vgbel,lips-vgpos)
    from    lips
   where         vbeln          Eq p_zlcpp-vbeln
     and         matnr          Eq p_zlcpp-matnr
     and         charg          Eq p_zlcpp-charg

The secondary index ZB has the fields as follows:

MANDT	Client
MATNR	Material Number
CHARG	Batch Number
BWART	Movement Type (Inventory Management)

When seen in the trace it seems the select query picks this secondary index. Whether it is correct? since i find no BWART in the select query. Because of this index the query take large time. I used the Hint statement as follows:

select  single lgort       vgbel        vgpos
    into   (lips-lgort,lips-vgbel,lips-vgpos)
    from    lips
   where         vbeln          Eq p_zlcpp-vbeln
     and         matnr          Eq p_zlcpp-matnr
     and         charg          Eq p_zlcpp-charg   %_HINTS ORACLE 'INDEX("LIPS" "LIPS~0")' .

and it now works fast. Please advice the best way to make the select query work on itself without using the hint statement to pick the index LIPS~0 instead of the invalid secondary index ZB.

Thanks & Regards,

Selvakumar M.

Edited by: Selva on Jun 17, 2011 7:35 PM