Re: Suitable index for the query

  • From: Alex Octan <alex@xxxxxxxxxxx>
  • To: Oracle I List <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 26 Jun 2010 10:53:02 -0300

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: