Re: Case (in)sensitivity in queries other than upper()...

If you are wanting to do a case insensitve search of a table's column, while avoiding full table scans, I would suggest the following:

1. Store the column's data in the database as upper or lower, then you can make the search parameter match.
2. If you're systems requirements wont allow the column to be stored as all upper or lower case, then you can make a second relation that has the primary key from the base table and any columns you want to search. UPPER or LOWER these columns. You can build indexs on them and put on update, insert, and delete triggers on the main table to keep the lookup table in sync. This is obviously not as efficient a solution as method 1, but it will give better performance than full table scans if the table is of a reasonable size.

Hope this helps :)

--
Kenneth E. Murphy
University of Missouri - Columbia
Office of Social & Economic Data Analysis
kenneth_at_oseda.missouri.edu
(573) 884-8794

Raj Gabriëlse wrote in message <34E6DF04.EC878E3_at_ctbede.nl>...


>Hi all,

>

>There's one thing I'd like to be possible to do, and that is to have the

>ability to query for data without having to worry about case and also

>not having to pay the performance hit when using upper() and thereby

>loosing the use of present indices.

>

>Is there a way to make Oracle case insensitive other than upper()? I

>guess I'd have to fiddle with the NLS_SORT setting and create a special

>kind of NLS table?

>

>TIA

>

>Raj Gabrielse

>