Oracle 11 and NLS Indexes

Folks,

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