nls_sort和nlssort 排序功能介绍

nls_sort和nlssort 排序功能介绍

博客分类:

  • oracle

 

ALTER SESSION SET NLS_SORT=”;
排序影响整个会话 
Oracle9i之前,中文是仍次上前制编码进行排序的。   
    
 
  在oracle9i中新增了本拼音、部首、笔画排序功能。设置NLS_SORT值     
    
    SCHINESE_RADICAL_M  
按照部首(第一挨家挨户)、笔划(第二挨家挨户)排序     
    
    SCHINESE_STROKE_M  
按照笔划(第一梯次)、部首(第二梯次)排序     
    
    SCHINESE_PINYIN_M  
按照拼音排序 
oracle9i中新增了照拼音、部首、笔画排序功能 
拼音 SELECT * FROM TEAM ORDER BY
NLSSORT(列名,’NLS_SORT = SCHINESE_PINYIN_M’) 
笔划 SELECT * FROM TEAM ORDER BY
NLSSORT(列名,’NLS_SORT = SCHINESE_STROKE_M’) 
部首 SELECT * FROM TEAM ORDER BY
NLSSORT(列名,’NLS_SORT = SCHINESE_RADICAL_M’) 

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_COMPNLS_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’)”)指定排序方法

相关文章