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?
>
> Thanks,
>
> Jeff Kish
> from a place with no DBA
> I'm just a programmer (C++) ; - )
>
> ait_at_concentric.net

You're right - using UPPER(pathcolumnname) will mean you cannot use an index on pathcolumnname.

One solution that might work is to have two columns in your table: ActualPathname and UppercasePathname. Then you can do something like:

SELECT ActualPathname FROM the_table WHERE UppercasePathname = UPPER(somestring)

You would be able to use an index on UppercasePathname. You'd only encounter problems if someone on a case-sensitive filesystem decided to have two files whose names only differed in their case, and to honest if they're going to do that they deserve to have problems :-)

Oliver. Received on Mon Jun 02 1997 - 00:00:00 CDT