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: Upper Case Indexes

Re: Upper Case Indexes

From: Joseph S. Testa <teci_at_oracle-dba.com>
Date: Mon, 25 Sep 2000 14:21:21 -0400
Message-Id: <10630.117781@fatcity.com>


dont forget to:

Note:

            You must set the QUERY_REWRITE_ENABLED session parameter to true to enable function-based indexes for queries. If

            QUERY_REWRITE_ENABLED is false, then function-based indexes are not used for obtaining the values of an expression in

            the function-based index. However, function-based indexes can still be used for obtaining values in real columns.

            QUERY_REWRITE_ENABLED is a session-level and also an instance-level parameter.

joe

Oliver Artelt wrote:

> How about:
>
> Function-based indexes defined on UPPER(column_name) or LOWER(column_name)
> can facilitate case-insensitive searches. For
> example, the following index:
>
> CREATE INDEX uppercase_idx ON emp (UPPER(empname));
>
> can facilitate processing queries such as this:
>
> SELECT * FROM emp WHERE UPPER(empname) = 'RICHARD';
>
> check it out,
> oli
>
> On Mon, 25 Sep 2000, Stuart Houlston wrote:
> > All,
> >
> > I have a problem with searching a name column within the database where the
> > case of the data must be preserved. This name is then used in the search
> > criteria of many queries in the application.
> >
> > The data is
> >
> > Table DEALERS
> > Column NAME
> > Data
> > BMW Freecars Ltd
> > BMW FREECARS ltd
> > BMW FreeCars LTD
> >
> > Table DEALERS will contain several thousand entries and an index is placed
> > on the NAME column to assist with lookups.
> > These are all valid entries but the developers want to be able to search on
> >
> > SELECT *
> > FROM DEALERS
> > WHERE NAME = 'BMW FREECARS LTD'
> >
> > This should bring back the 3 rows above.
> >
> > I could of course use the UPPER function around the column name but this
> > will invalidate the use of my carefully designed index strategy and cause
> > the query to take a thousand years to complete (well a few seconds anyway).
> >
> > The only suggestion that I have come up with is to have a second column
> > called UPPER_NAME on the table that hold all the values from NAME in
> > UPPERCASE. This could then be indexed and used in the search criteria. This
> > column will be maintained by the use of database triggers. A bit messy but
> > it should work.
> >
> > Any other helpful hints and tips would be gratefully accepted.
> >
> >
> > Many Thanks
> >
> > Stuart
>
> --
> ---
>
> Oliver Artelt, System- und Datenbankadministration
> ---------------------------------------------------------------
> cubeoffice GmbH & Co.KG # jordanstrasse 7 # 39112 magdeburg
> telefon: +49 (0)391 6 11 28 10 # telefax: +49 (0)391 6 11 28 19
> email: oli@cubeoffice.de # web: http://www.cubeoffice.de
> ---------------------------------------------------------------
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Oliver Artelt
> INET: oli_at_md.transnet.de
>
> 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
Received on Mon Sep 25 2000 - 13:21:21 CDT

Original text of this message

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