Hi Stephane, what you said makes total sense, the way you put is right. I was just wondering if I could improve more the performance, ... the other columns are indexed and with the statistics up-to-date. Anyway, thanks a lot for putting the explanation this way, very very helpful. Alex On Fri, Jun 25, 2010 at 17:58, Stephane Faroult <sfaroult@xxxxxxxxxxxx>wrote: > Alex, > > 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; > > > > > > >