Case Insensitive Queries

Some databases have an "ignore case" flag that can be set for the entire database. Oracle does not, and thus case-insensitive queries have long caused problems, not with coding them, but with their performance (since indexes are typically not used to determine the result).

Its relatively straightforward to create a case-insensitive query:

SQL> select *
2 from EMP
3 where upper(ENAME) = upper(:b1)

but of course (by default) the "UPPER(ENAME)" cannot take advantage of an index that may have been defined on the ENAME column.

Enter 8i, where the concept of a function-based index is now possible. Before you rush off and try to create them, take note of the following:

* You must have the system privelege query rewrite to create function based indexes on tables in your own schema.
* You must have the system privelege global query rewrite to create function based indexes on tables in other schemas
* For the optimizer to use function based indexes, the following session or system variables must be set:
QUERY_REWRITE_ENABLED=TRUE
QUERY_REWRITE_INTEGRITY=TRUSTED
* You must be using the Cost Based Optimiser (which means analyzing your tables/indexes)

and then its just a case of creating the index in the conventional way:

create index UPPER_ENAME_IX on ENAME ( UPPER(ENAME) ) ;

Note that this index will NOT be used for case-SENSITIVE queries. You could always have two indexes, one on ENAME and one on UPPER(ENAME), but it would probably be more efficient to just have the function-based one and code:

SQL> select *
2 from EMP
3 where upper(ENAME) = upper(:b1)
4 and ENAME = :b1

for the times where you do not want case-insenstivity.


Thanks to (http://www.jlcomp.demon.co.uk/faq/case_insensitive_query.html)