Re: Oracle 7.0: Case insensitive string comparisons?

In article <758036583snz_at_pro-data.demon.co.uk> Chrisj_at_pro-data.demon.co.uk writes:
>In article <7JAN199410585816_at_cccs.umn.edu> mcj_at_cccs.umn.edu writes:
>
>>
>>This is a fairly standard problems but I do not know of an Oracle solution.
>>
>>Potential solutions we have considered:
>> Store names/addresses in normal format, e.g Mark Jones
>> Problem indexed search will not find match if search criteria
>> entered as MARK JONES, and if we force the database column to
>> uppercase in the comparison then index will not be used.
>>
>Can't you make the index on _at_upper() of the column?
>--
>Chris Jack

Noop :
SQL> create index gerrit on booking(upper(booking_nr)); create index gerrit on booking(upper(booking_nr))

                                    *

Another solution is : create an extra column e.g. upper_name. If you are using oracle 7 : define a trigger which on update/insert of column name automatically updates the upper_name column and for selects always use the upper_name...

From one point of view it is quite logical that you create indexes on columns and not on things like upper(). But for some occasions it might be useful to have indexes on expressions as well. I guess this could make your inserts and updates fairly slow if you have to make computations first and then alter your index data, especially if your index is created as on (sin(value1)+cos(value2)-value1*value2) !!!

I believe that if a computation on one or more columns is used frequently (like an upper or so) it makes it worth a while to store the computed value as wel as the original values. Once you've crossed the line where you do more computations on values of columns for select purposes than you would do the same computations when inserting or updating rows I would strongly suggest to store the computed value as an extra column. As a bonus you can then create indexes on the computations.

-- 
Gerrit Cap				
Vlaamse Televisie Maatschappij N.V.		e-mail :   gerrit_at_vtm.be
Medialaan 1					fax    : +32 2 253.12.21
B-1800 Vilvoorde Belgium			voice  : +32 2 255.38.72