Re: Oracle 11 and NLS Indexes

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-linguistic-indexes-part-i/ http://richardfoote.wordpress.com/2008/01/09/introduction-to-linguistic-indexes-part-ii/

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


> 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

>