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