Indexes for Case Insensitive search
- From: Martin Klier <usn@xxxxxxxxx>
- To: Oracle L <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 01 Feb 2010 15:53:18 +0100
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
http://www.usn-it.de
--
//www.freelists.org/webpage/oracle-l
Other related posts:
