Re: Suitable index for the query
- From: Craig Dickman <craig_dickman@xxxxxxxxx>
- To: alex@xxxxxxxxxxx, Oracle I List <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 25 Jun 2010 13:57:04 -0700 (PDT)
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@xxxxxxxxxxx>
To: Oracle I List <oracle-l@xxxxxxxxxxxxx>
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;
Other related posts:
