How do you write a query that ignores the effects of upper and lower case ?

The Oracle (tm) Users' Co-Operative FAQ

How do you write a query that ignores the effects of upper and lower case ?

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.

Further reading: Querying the dictionary for function-based indexes