Oracle Case insensitive index searches Don Burleson

For more details on tuning Oracle with indexes, see my book "Oracle Tuning: The Definitive Reference".

There are several techniques to write case insensitive SQL that avoids index invalidation and unnecessary full-table scans.  You can also employ Oracle text indexes to remove full-table scans when using the LIKE operator.  Prior to Oracle10g release 2 case insensitive queries required special planning:

1 - Transform data in the query to make it case insensitive (note that this can invalidate indexes without a function-based index):

create index upper_full_name on customer ( upper(full_name));

select full_name from customer
where upper(full_name) = 'DON BURLESON';

2 - Use a trigger to transform the data to make it case insensitive (or store the data with the to_lower or to_upper BIF.

3 - Use Alter session commands:

alter session set NLS_COMP=ANSI;
alter session set NLS_SORT=GENERIC_BASELETTER;
select * from customer where full_name = 'Don Burleson'

Oracle 10g release 2 has now introduced a case insensitive search method using BINARY_CI and BINARY_AI sorts.

In Oracle10g release 2 we see this new approach to case insensitive searches. (This example is derived from Tom Kyte's Oracle OpenWorld 2004 presentation on Oracle10g release 2 new features)

Initialization parameters:

NLS_SORT=binary_ci
NLS_COMP=ansi

Sample index create:

create index
   caseless_name_index
on
   customer
(
   nlssort( full_name, 'NLS_SORT=BINARY_CI')
);

alter session set nls_sort=binary_ci;
select * from customer where full_name = 'Don Burleson';