Re: nlssort problem

MadeOfRose <mehmetgulden_at_gmail.com> wrote:
> i have got a problem about nlssort statement.
>
> i use oracle 10g r2 (32 bit on amd64) with windows 2003 server r2 (32
> bit)
> we have special characters in turkish (i dont know you can see
> correctly.they are;öÖçÇiIsSüÜgG)
>
> i create a database with windows 1254 character set(8 bit) which
> supports these special character. But, when i use select with ordey by
> keyword then the words which start with turkish character show up at
> the bottom of the results.
>
> After that i invent the nlssort :P(i guess it is a function).it works
> when use it like below
>
> select * from GECOUST order by NLSSORT(ad,'NLS_SORT=XTURKISH')
>
> it seem ok.But,i also see a usage at documents of oracle.it says 'u can
> use NLSSORT with create index statement' and i use it.
>
> CREATE INDEX nls_index ON my_table (NLSSORT(name, 'NLS_SORT =
> XTURKISH'));
>
> but it doesnt work.
>
> can anybody help me.i wanna use it with create index statement so i
> dont have to use NLSSORT statement with ORDER BY everytime.

You are mixing some things up here.

Creating an index in a specific sort order will not influence how query results are sorted.

It will only influence whether the index is used or not if you sort in a specific sort order.

What you need to do is set NLS_SORT=XTURKISH in your session parameters: ALTER SESSION SET NLS_SORT=XTURKISH; Then the following query will do what you want: select * from GECOUST order by ad;

You can set NLS_SORT on different levels:

- As database initialization parameter via ALTER SYSTEM.
- As session parameter via ALTER SESSION.
- As session parameter via the environment variable NLS_SORT.

The session parameter will override the initialization parameter, so be careful.

What I would recommend if you want all sorts in Turkish is:

• Alter the initialization parameter NLS_SORT to XTURKISH. Then restart the instance.
• Set the environment variable NLS_SORT on your clielect * from GECOUST order bynt to XTURKISH.

Then your sort should work as desired, and the index should be used (verify it with an EXPLAIN PLAN).

Yours,
Laurenz Albe Received on Thu Apr 27 2006 - 10:14:55 CDT