Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Optimizer and function based index

Re: Optimizer and function based index

From: Ratnesh Kumar Singh <rks_oracle_at_rediffmail.com>
Date: Fri, 15 Feb 2002 07:53:40 -0800
Message-ID: <F001.00410F06.20020215064821@fatcity.com>

Oracle function based indexes do not work if OR clause is used .This is mentioned in the oracle docs.

I think if u use union clause it may work out . try and see...

> --- "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: Seefelt, Beth
> > INET: Beth.Seefelt_at_TetleyUSA.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).
>
> =====
> Connor McDonald
> http://www.oracledba.co.uk (mirrored at
> http://www.oradba.freeserve.co.uk)
>
> "Some days you're the pigeon, some days you're the
> statue"
>
> __________________________________________________
> Do You Yahoo!?
> Everything you'll ever need on one web page
> from News and Sport to Email and Music Charts
> http://uk.my.yahoo.com
> --
> Please see the official ORACLE-L FAQ:
> http://www.orafaq.com
> --
> Author: =?iso-8859-1?q?Connor=20McDonald?=
> INET: hamcdc_at_yahoo.co.uk
>
> 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).
 

--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Ratnesh  Kumar Singh
  INET: rks_oracle_at_rediffmail.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).
Received on Fri Feb 15 2002 - 09:53:40 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US