Re: CASE SENSITIVITY AND INDICES AND COLUMNS

jeff kish wrote:
>
> I am running Oracle 7.3 on Windows NT 3.51, with "normally" 3.51 NT on
> the workstations. My Oracle code is written in Proc-C 2.1
>
> we need to store file directory/file location information in a column
> in a table.
>
> I want to be able to tell if a user input location is already in the
> table.
>
> I anticipate many hundreds of rows in this table, eventually possibly
> thousands.
>
> should I:
> 1) force all data added to the column to be upper case
> 2) use select * where UPPER(pathcolumnname) = somestring
>
> If I use #1, then I worry that if something is remotely on some case
> sensitive file server (Unix maybe) that I won't be able to retrieve
> items later if I store them in upper case.
>
> If I use #2 then I worry that the select might take too long because
> . won't this make any index I have on the column unusable?
>
> Does anyone know what UPPER(column) does as far as the effect of an
> index on a select statement using that column?
>

To which Will Kooiman replied:

"Currently the best way to solve this problem is to do both. Store the name exactly as entered AND in upper case. This way you have the correct case of the file name for later use, and you can index the upper case column for searches.

upper() disables the use of indexes, just like all functions (almost all functions. trunc() on a date column doesn't disable indexes, but I think it's the only exception)."

Will is right, but if you're concerned about the amount of space this will take, you can add a flag to say whether the name as originally entered was all lowercase, all uppercase, or mixed case. Only when it was mixed case will you need to store the name as originally entered.

Hope this helps
Ken Denny
kdenny_at_interpath.com Received on Fri May 30 1997 - 00:00:00 CDT