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

> http://richardfoote.wordpress.com/2008/01/03/introduction-to-linguist......

> --
> Regards

> Jonathan Lewishttp://jonathanlewis.wordpress.com

> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html

> "Pat" <pat.ca...@service-now.com> wrote in message

> news:85839221-9b90-4c90-baf3-67f376c483bc@l42g2000hsc.googlegroups.com...

> > 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