?
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:
Normally, comparisons in the WHERE clause and in PL/SQL blocks is binary unless you specify the NLSSORT function.
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.
A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC
文章來源:
http://x-spirit.spaces.live.com/Blog/cns!CC0B04AE126337C0!569.entry