> 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