Re: Oracle can be Case Insensitive ?

"Roberto Leo" <roberto.leo_at_exm.it> wrote in message news:bj72dn$8l$1_at_newsread.albacom.net...
> Oracle can be case insensitive ? What Options Should I set to be case
> insensitive in the Oracle Database ?
>
> Thanks Rob.
>

No, Rob, it can't be case insensitive, because we live in a case sensitive world.

Alright, there are ways and means of achieving something similar, but there is no 'CASE_INSENSITIVE=TRUE' init.ora parameter or anything like that.

For example, if you have a query which says select * from emp where ename = 'Bob', and you wish it to be case insensitive, you can issue it instead as select * from emp where upper(ename)='BOB'. That, of course, will provoke a full table scan, even with an index on the ename field, because the index contains "Bob", not "BOB".

So in 8i, they invented function-based indexes. Create index blah on emp upper(ename). So now you have an index which *does* contain an entry for "BOB", and the earlier query is thus able to make use of index access whilst doing its case insensitive search.

In earlier versions, one tended to add an extra set of columns to the table, and create triggers which, on an insert or update, inserted an upper case version of the relevant data into those extra columns.

So a piecemeal approach is possible, but no quick fix.

Regards
HJR Received on Thu Sep 04 2003 - 05:08:38 CDT