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: Rachel Carmichael <carmichr_at_hotmail.com>
Date: Mon, 25 Sep 2000 17:17:29 GMT
Message-Id: <10630.117766@fatcity.com>


Stuart...

This is a PERFECT case for using a function-based index, on the assumption that you are running 8i and can do so...

Function-based indexes do not change the case of the data in the table, but you can create an index with the upper function on the column.

Rachel

>From: Stuart Houlston <Stuart.Houlston_at_bmw.co.uk>
>Reply-To: ORACLE-L_at_fatcity.com
>To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
>Subject: Upper Case Indexes
>Date: Mon, 25 Sep 2000 08:51:13 -0800
>
>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
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.com
>--
>Author: Stuart Houlston
> INET: Stuart.Houlston_at_bmw.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).



Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com.

Share information about yourself, create your own public profile at Received on Mon Sep 25 2000 - 12:17:29 CDT

Original text of this message

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