oracle数据库拼音排序及NLS_SORT配置[转]

　　　　Oracle9i之前，中文是按照二进制编码进行排序的。在oracle9i中新增了按照拼音、部首、笔画排序功能。
　　1、设置NLS_SORT参数值
      SCHINESE_RADICAL_M 按照部首（第一顺序）、笔划（第二顺序）排序
      SCHINESE_STROKE_M 按照笔划（第一顺序）、部首（第二顺序）排序
      SCHINESE_PINYIN_M 按照拼音排序
　　2、Session级别的设置，修改ORACLE字段的默认排序方式：
      按拼音：alter session set nls_sort = SCHINESE_PINYIN_M;
      按笔画：alter session set nls_sort = SCHINESE_STROKE_M;
      按偏旁：alter session set nls_sort = NLS_SORT=SCHINESE_RADICAL_M；
　　3、语句级别设置排序方式：
      按照笔划排序
      select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_STROKE_M');
      按照部首排序
      select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_RADICAL_M');
      按照拼音排序，此为系统的默认排序方式
      select * from dept order by nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M');
　　4、修改系统参数（数据库所在操作系统）：
      set NLS_SORT=SCHINESE_RADICAL_M ;
　　export NLS_SORT （sh）
       setenv NLS_SORT SCHINESE_RADICAL_M (csh)
      HKLC\SOFTWARE\ORACLE\home0\NLS_SORT (win注册表)
　　
　　
　　Oracle 官方说明
　　
　　NLS_SORT NLS_SORT specifies the collating sequence for ORDER BY queries. NLS_COMP NLS_COMP specifies the collation behavior of the database session.
　　
　　

Property	Description
Parameter type	String
Syntax	NLS_SORT = { BINARY | linguistic_definition }
Default value	Derived from NLS_LANGUAGE
Modifiable	ALTER SESSION
Range of values	BINARY or any valid linguistic definition name

　　

• If the value is BINARY, then the collating sequence for ORDER BY queries is based on the numeric value of characters (a binary sort that requires less system overhead).
  
• 　　If the value is a named linguistic sort, sorting is based on the order of the defined linguistic sort. Most (but not all) languages supported by the NLS_LANGUAGE parameter also support a linguistic sort with the same name.
  　　Note:
  　　Setting NLS_SORT to anything other than BINARY causes a sort to use a full table scan, regardless of the path chosen by the optimizer. BINARY is the exception because indexes are built according to a binary order of keys. Thus the optimizer can use an index to satisfy the ORDER BY clause when NLS_SORT is set to BINARY. If NLS_SORT is set to any linguistic sort, the optimizer must include a full table scan and a full sort in the execution plan.
  

　　You must use the NLS_SORT operator with comparison operations if you want the linguistic sort behavior.
　　
　　

Property	Description
Parameter type	String
Syntax	NLS_COMP = { BINARY | LINGUISTIC | ANSI }
Default value	BINARY
Modifiable	ALTER SESSION
Basic	No

　　
　　Values:

• BINARY
  

　　Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.

• LINGUISTIC
  

　　Comparisons for all SQL operations in the WHERE clause and in PL/SQL blocks should use the linguistic sort specified in the NLS_SORT parameter. To improve the performance, you can also define a linguistic index on the column for which you want linguistic comparisons.

• ANSI
  

　　A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC
　　
　　注意：当使用了NLS_SORT或NLS_COMP，应该指定函数索引来提高数据库性能。如
　　create index [schema.]indexName on tableName(function(propertyName))
　　
　　注意：虽然能在oracle查询的时候能用得上，但不知道为什么，当映射到hibernate上时却无效，依然按NLS_SORT=binary排序，不过可以使用@org.hibernate.annotation.OrderBy("nlssort(name,'NLS_SORT=SCHINESE_PINYIN_M')")指定排序方法