Re: Oracle 11 and NLS Indexes
Date: Sat, 12 Apr 2008 19:58:37 +0100
Linguistic indexes do support "like" in11g, although
is not something you would expect an efficient path for, as it has to check every entry anyway, however
will do the range scan you would hope for.
A good starting point for a little reading would be Richard Foote's blog:
-- Regards Jonathan Lewis http://jonathanlewis.wordpress.com Author: Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html "Pat" <pat.casey_at_service-now.com> wrote in message news:85839221-9b90-4c90-baf3-67f376c483bc_at_l42g2000hsc.googlegroups.com...Received on Sat Apr 12 2008 - 13:58:37 CDT
> 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>,
> 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
> 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