Re: Suitable index for the query
Date: Sat, 26 Jun 2010 10:16:22 -0700 (PDT)
Message-ID: <780063.82642.qm_at_web30606.mail.mud.yahoo.com>
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_at_octan.cz.cc>
To: Oracle I List <oracle-l_at_freelists.org> 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_at_roughsea.com> 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;
>>>
>>>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Sat Jun 26 2010 - 12:16:22 CDT