Re: Oracle 11 and NLS Indexes

On Apr 12, 11:58 am, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> 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:
>
> http://richardfoote.wordpress.com/2008/01/03/introduction-to-linguist...http://richardfoote.wordpress.com/2008/01/09/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..._at_service-now.com> wrote in message
>
> news:85839221-9b90-4c90-baf3-67f376c483bc_at_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

Thanks Jonathan, that helps a bit. I didn't expect we could avoid an index full scan w/o some form of text index, but its nice to know that I'll at least be able to avoid a table scan w/o adding those bizarre double indexes. Received on Sat Apr 12 2008 - 17:21:54 CDT