Re: Suitable index for the query
- From: Alex Octan <alex@xxxxxxxxxxx>
- To: Oracle I List <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 30 Jun 2010 14:41:12 -0300
Hi Dave,
but I think your explanation has total sense because the table is not that
big.
it has 54672 rows, 229 avg_row_len and 1914 blocks.
Due to a small table, its much faster to read a full table instead of 2
reads.
I was just wondering how I could improve situations like this, for this
mentioned case I believe that is fine, and for all other situations many
thoughts we had. That's great to hear and to share.
Thanks everybody.
Alex
On Sat, Jun 26, 2010 at 14:16, Dave Pacia <davepacia@xxxxxxxxx> wrote:
> 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;
>> >
>> >
>>
>>
>>
>
>
>
Other related posts:
