Re: Suitable index for the query

Why do you think that an index would make things run faster? And let me tell you that applying the Monte-Carlo method to indexing is unlikely to give much result.
tran_level and item_level belong to the same table, therefore you can ignore them. Your query is like going to a library and saying "what are the books say about databases in which I find three identical words on page 20 and 50?" All you can do is get all the database books and check them one by one when you have them. Do you see what I mean? In my comparison, one book is just like one row from item_master in your case. You don't know before getting the row what tran_level holds anymore than you know before getting the book which words are on page 20. Indexing is useless.

 Your entry point is item_loc.loc - I assume boldly (from the names) that's the primary key. You can therefore get the item_loc fast, unless it is defined as a varchar2, in which case your query would lack quotes. Let's say you have this row, and the value for 'item' that it contains. How selective is it? Can you find this value in many rows of item_master, or not? If it's not selective, nothing wrong in running a full scan. If it's selective, have you an index on it? Does Oracle know it is selective? Have you computed stats on the index? If the values is sometimes selective and sometimes not, tough luck, I doubt that even histograms would help you because the optimizer cannot decide beforehand how popular is a value that it retrieves in the process of the join.

Hope that helps,

Stephane Faroult
RoughSea Ltd <http://www.roughsea.com>
Konagora <http://www.konagora.com>
RoughSea Channel on Youtube <http://www.youtube.com/user/roughsealtd>

Alex Octan wrote:
> 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