Re: Suitable index for the query

From: Dave Pacia <>
Date: Sat, 26 Jun 2010 10:16:22 -0700 (PDT)
Message-ID: <>

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,

From: Alex Octan <>
To: Oracle I List <> 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.


On Fri, Jun 25, 2010 at 17:58, Stephane Faroult <> wrote:

>>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
>> 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 <>
>>Konagora <>
>>RoughSea Channel on Youtube <>
>>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;

Received on Sat Jun 26 2010 - 12:16:22 CDT

Original text of this message