We've got an application that runs right now on Oracle 10g. The app
runs with NLS sorting and comparisons on e.g.

ALTER SESSION SET NLS_COMP=LINGUISTIC
ALTER SESSION SET NLS_SORT=BINARY_CI

To make this work efficiently, we added a large number of functional
indexes on commonly access colums e.g.

create index <something> on <table> (nlssort(<field>,
'nls_sort='binary_ci')

By and large we've been happy with this result under 10g; it gives the
right result reasonably efficiently (although if you've been following
my other posts, we did run into some weirdness with 10.2.0.4).

There are a couple of issues which thought which, while not critical,
have been a bit of a chronic nuisance. I've been reading in some of
the doc on Oracle 11 that these might have been resolved, but I was
hoping somebody here might be able to offer some real world
experience.

Issue #1: LIKE operator doesn't seem to benefit from an nls index. I
dunno if this is an optimizer thing or something about how the NLS
indexes work, but a query like:

SELECT * from dog where breed LIKE '%hound%'

won't benefit from an NLS index on breed. So for a couple of high
volume queries, I have the deeply unnatural index structure of:

create index this_is_unnatural on dog(nlssort(breed,
'nls_sort='binary_ci''), breed);

By having both the nls and binary versions of breed in the index, I
can use the first half of it for normal queries, and the second
(binary) half lets Oracle resolve like queries via an index full scan
instead of a full table scan. Kind of makes my skin crawl though since
it feels wrong to have reduncant indexes in place like that :(.
Leading with the NLS index also means keys come out sorted by breed,
so I can resolved ordered like queries w/o a sort.

One of the DBAs I work with mentioned that this wasn't necessary
anymore in 11 since it could use the nls index for the like operator,
but since I don't have an 11 test environment set up I couldnt'
verify. Does anybody here have any experienced with this? If so, could
you share some experience on how it works?

Issue #2: NLS indexes just seem "slower" than binary indexes. I don't
have the numbers to prove it (or really the interest in doing so), but
the subjective evaluation is that things like range scans or full
scans across an nls index take significantly more time than an
analagous scan across a binary index. This may be a case of me blaming
the "newfangled feature" though since, I'm more familiar with the
traditional approach of shadowing a column with toUpper(<breed>) and
then querying and sorting against the shadow column. NLS indexes are
something we just started using in 10g and, to be honest, I'm not sure
understand them as well as I should. I can usually predict the
behavior of binary indexes pretty well, but with NLS index I still get
shocked from time to time with "why the heck did the optimizer do it
*that* way?"

As you can probably tell, we're in the very early stages of looking
into migrating to 11, but it's a case of I don't know enough to even
start putting together a migration and test plan. I can't even answer
questions like "would a reasonable person expect our workload to run
more or less efficiently under 11?"

In any event, if anybody has any experience to share with migrating an
NLS dependent workflow from 10g to 11, I'r really love to hear about
it. Do the things I mentioned above get better? Do other things
improve? Any gotchas, etc.

Any insight would be appreciated.

--- Pat