Re: Oracle 11 and NLS Indexes

From: Jonathan Lewis <>
Date: Sat, 12 Apr 2008 19:58:37 +0100
Message-ID: <>

Linguistic indexes do support "like" in11g, although

    like '%abcx%'
is not something you would expect an efficient path for, as it has to check every entry anyway, however

    like 'abc%'
will do the range scan you would hope for.

A good starting point for a little reading would be Richard Foote's blog:


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

"Pat" <> wrote in message

> Folks,
> We've got an application that runs right now on Oracle 10g. The app
> runs with NLS sorting and comparisons on e.g.
> 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
> 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
Received on Sat Apr 12 2008 - 13:58:37 CDT

Original text of this message