Re: Suitable index for the query

I may be missing something, but since you're querying the il.loc column, shouldn't your index be on that column?

-Craig




________________________________
From: Alex Octan <alex_at_octan.cz.cc>
To: Oracle I List <oracle-l_at_freelists.org>
Sent: Fri, June 25, 2010 1:28:39 PM
Subject: Suitable index for the query


Hi there,

I am struggling to find a way to optimize the performance of my query to use index when involving 3 columns.
I highlighted the columns that is being used on my query, however I tried to create index with different combinations but the db doesnt use.

The indexes that i tried to create, the execution plan didn't use any of them.
So, my doubt is how can I improve performance on this query? cause its getting full table scan

Any thoughts is very welcome.
Thanks all
Alex


. . .

FROM item_loc il, 
          item_master im    --------------------> table access full
WHERE il.loc = 2836
AND il.item = im.item       -----------------> one column to include in the clause
AND im.tran_level = im.item_level    ---> two columns to include in the clause


create index ITEM_MASTER_I9 on ITEM_MASTER (item, tran_level, item_level) tablespace RETEK_CAT_INDEX;

create index ITEM_MASTER_I9 on ITEM_MASTER (item, tran_level) tablespace RETEK_CAT_INDEX;

create index ITEM_MASTER_I9 on ITEM_MASTER (tran_level, item_level) tablespace RETEK_CAT_INDEX;

create index ITEM_MASTER_I9 on ITEM_MASTER (item, item_level) tablespace RETEK_CAT_INDEX;

--
http://www.freelists.org/webpage/oracle-l