Re: Index question
Date: 31 Aug 1993 19:07:42 GMT
Message-ID: <2607hv$jrd_at_news.mic.ucla.edu>
In article <1993Aug31.182209.18739_at_news.unomaha.edu> moswald_at_cwis.unomaha.edu (Mike Oswald) writes:
>Hans Kristian Ruud writes:
>> I just got asked the following question:
>>
>> Suppose one has a table with a CHAR column. This column will contain both
>> uppercase and lowercase text. Is it possible to put an index on the value of
>> UPPER(field), rather than on the field itself?
>>
>> My belief is that it is not possible to do so, however, I would like the
>> opinion of the net.
>
>I don't believe you can ... in fact if you stored you u/l data and tried
>to search on that field you would have to have an identical match. Why
>not store it as upper case and return/display the info with INIT()?
>
>Don't forget using UPPER() in your WHERE clause will cause Oracle to ignore
>the index.
>
>--Mike
This should probably be in the FAQ. The capability to do caseinsensitive searches and still use an index is a long running enhancement request at Oracle. What you can do with Oracle7 is to use Post-Insert, Delete, and Update triggers to maintain an indexed, uppercase version of the column in the same table.
CREATE TABLE foo
(
first_name varchar(40), last_name varchar(40),
...
last_name_upper varchar(40));
You'll pay in storage space but you'll gain in access speed.
Do any of the other DBMS products provide a case-insensitive indexed query ?
Hope this helps,
- Dan
Daniel Druker
Anderson Graduate School of Management at UCLA
| Dan Druker | | agsm mail : ddruker | | internet : ddruker_at_agsm.ucla.edu | | oracle*mail : unix:ddruker_at_agsm.ucla.edu | ----------------------------------------------------------------------------
Disclaimer: None. I'm a student now and I don't care what you think. Received on Tue Aug 31 1993 - 21:07:42 CEST