Indexes for Case Insensitive search

From: Martin Klier <>
Date: Mon, 01 Feb 2010 15:53:18 +0100
Message-ID: <>

Dear Listers,

I'm operating with databases 10gR2 and above. We are considering to change our internal software development standard into case insensitive behaviour. Destination is, to work with NLS_SORT=BINARY_CI and NLS_COMP=LINGUISTIC and we sometimes do EQUAL (WHERE NAME='J. Random Hacker') search/join/access on indexed varchar2 fields. FYI, the old approach was to use lower('') everywhere. I hope to save a bit of ressources, and also hope to become more portable.

LIKE statements can occur too, but since I can't really support them by index, they are out of line here.

My question is:
I've seen that I need to create the index with (NLSSORT(NAME,'NLS_SORT=BINARY_CI')). Is there a disadvantage coming from the need to create proper (means function based) indexes for these columns? If yes, especially what should I take care of?

A second question:
Is there a way to avoid this function based approach? Is it possible that 10g/11g databases create the indexes properly (following the NLS_SORT init variable) with no need to involve NLSSORT() in index DDL? This would be nice, since this would save script changing effort in migration scenarios.

I'm grateful for any ideas on this topic, Thanks a lot in advance and best regards

Martin Klier

Usn's IT Blog for Linux, Oracle, Asterisk

Received on Mon Feb 01 2010 - 08:53:18 CST

Original text of this message