Re: Upper Case Indexes

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).