case insensitive Suche unter Oracle

Bei Tom Kyte (wo sonst?) findet man folgende hübsche Möglichtkeit, in 10g case eine insensitive Konditionsprüfung zu ermöglichen. Relevant wäre diese Option beispielsweise dann, wenn man die Statements einer Applikation nicht ändern - und die Prüfung z.B. nicht mit einem UPPER beeinflussen - kann.

create table t ( data varchar2(20) );

insert into t values ( 'Hello' );
insert into t values ( 'HeLlO' );
insert into t values ( 'HELLO' );

-- wie zu erwarten liefert die folgende Query
-- zunächst kein Ergebnis
select * 
  from t
 where data = 'hello'

 Es wurden keine Zeilen ausgewählt

alter session set nls_comp=ansi;

Session wurde geändert.

alter session set nls_sort=binary_ci;

Session wurde geändert.

-- Nach Anpassung der beiden NLS-Parameter 
-- wird die Bedingung case insensitive behandelt
select * 
  from t
 where data = 'hello'

DATA
--------------------
Hello
HeLlO
HELLO

3 Zeilen ausgewählt.

set autot trace
select * 
  from t
 where data = 'hello'

3 Zeilen ausgewählt.

Ausführungsplan
--------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------
| Id | Operation | Name | Rows | Bytes |
--------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 |
|* 1 | TABLE ACCESS FULL| T | 1 | 12 |
--------------------------------------------------

-- durch die Anlage eines function based index kann man den
-- Zugriff dann auch noch optimieren.
create index t_idx on t( nlssort( data, 'NLS_SORT=BINARY_CI' ) );

select * 
  from t
 where data = 'hello';

3 Zeilen ausgewählt.

Ausführungsplan
-----------------------------------------------------
Plan hash value: 470836197
-----------------------------------------------------
| Id | Operation | Name | Rows |
-----------------------------------------------------
| 0 | SELECT STATEMENT | | 1 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 |
|* 2 | INDEX RANGE SCAN | T_IDX | 1 |
-----------------------------------------------------

Zu Prüfen wäre in einem solchen Fall nur, welche weiteren Auswirkungen die Änderung der NLS-Parameter auf eine Applikation haben könnte. Zusätzliche Details zum Thema findet man im AskTom-Thread.