Re: Optimizer and function based index

Can also try UNION.

Connor McDonald wrote:
>
> Add a hint to force it to use the index, and then
> check the costs - this may assist in telling you why
> the index was not chosen.
>
> Also, maybe see what happens using IN instead of OR
>
> hth
> connor
>
> --- "Seefelt, Beth" <Beth.Seefelt_at_TetleyUSA.com>
> wrote: >
> > Hi everybody,
> >
> > I'm hoping someone can help me understand this.
> >
> > I have this query -
> >
> > SELECT ORDERID FROM WWW SHOPCART
> > WHERE UPPER(MEMBERNAME) ='198917777' ;
> >
> > I added an index on the column UPPER(MEMBERNAME) and
> > it works great.
> >
> > I have another query -
> >
> > SELECT ORDERID FROM WWW SHOPCART
> > WHERE UPPER(MEMBERNAME) ='198917777' OR
> > UPPER(MEMBERNAME)='1989155555';
> >
> > and this query will not use the index at all.
> >
> > I can't understand why the optimizer won't choose to
> > use the function based index when there is an OR
> > clause. I've tried the same query with just
> > MEMBERNAME= and using an index on MEMBERNAME, and
> > the optimizer will use the index if I OR my
> > criteria together.
> >
> > Any ideas?
> >
> > Thanks,
> >
> > Beth
> >

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Stephane Faroult
  INET: sfaroult_at_oriole.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).