Alex, How many rows in the item_master table? What is the avg_row_len? How many blocks in the table? If the table is small, a full table scan may be more efficient than via index. Instead of 2 reads, one for the index followed by a second for the table, Oracle can get the job done with a single read. If we know that the table is going to be accessed via three columns, we build indexes in the order of decreasing cardinality: column with largest number of distinct values first, followed by the second, etc. Out of curiousity, what is the Oracle version and operating system? Best Regards, Dave ________________________________ From: Alex Octan <alex@xxxxxxxxxxx> To: Oracle I List <oracle-l@xxxxxxxxxxxxx> Sent: Sat, June 26, 2010 9:53:02 AM Subject: Re: Suitable index for the query 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; >>> >>> > > >