Home » RDBMS Server » Performance Tuning » composite index (Oracle 11.1.0.7.0 64 bit)
composite index [message #507140] Thu, 12 May 2011 12:47 Go to next message
yassirrehman@gmail.com
Messages: 9
Registered: February 2008
Location: USA
Junior Member
I have created an non unique index lk_fein on lookup_fein(
code,map_id,trash). When I check the explain plane it does a full table scan on lookup_fein. if I force it to use index by passing hint it does and the cost also decreases. Any idea what am I doing wrong here.

SQL> SELECT WORK_FEIN,
  2    NON_FEIN       ,
  3    FI_FEIN        ,
  4    MFEIN          ,
  5    TOTAL_FEIN     ,
  6    AC_TOTAL
  7     FROM
  8    (SELECT WORK_FEIN,
  9      NON_FEIN       ,
 10      FI_FEIN        ,
 11      MFEIN          ,
 12      TOTAL_FEIN     ,
 13      AC_TOTAL
 14       FROM LOOKUP_FEIN
 15      WHERE CODE=22
 16    AND MAP_ID  =-1
 17    AND (TRASH  =22
 18    OR TRASH   IS NULL)
 19   ORDER BY TRASH
 20    )
 21    WHERE rownum <=1	

Thanks.



[mod-edit: code tags added by bb; next time please add them yourself]

[Updated on: Thu, 12 May 2011 19:30] by Moderator

Report message to a moderator

Re: composite index [message #507141 is a reply to message #507140] Thu, 12 May 2011 12:52 Go to previous message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/

WHY MY INDEX IS NOT BEING USED
http://communities.bmc.com/communities/docs/DOC-10031

http://searchoracle.techtarget.com/tip/Why-isn-t-my-index-getting-used

http://www.orafaq.com/tuningguide/not%20using%20index.html
Previous Topic: Query Performance Issue
Next Topic: Help in tuning a value set query
Goto Forum:
  


Current Time: Fri Mar 29 00:18:35 CDT 2024