注册 登录  
 加关注
   显示下一条  |  关闭
温馨提示!由于新浪微博认证机制调整,您的新浪微博帐号绑定已过期,请重新绑定!立即重新绑定新浪微博》  |  关闭

断尘居

温柔的男人像海洋。

 
 
 
 
 

日志

 
 

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

2012-05-24 16:48:56|  分类: Oracle |  标签: |举报 |字号 订阅

  下载LOFTER 我的照片书  |

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')")指定排序方法

  评论这张
 
阅读(1201)| 评论(0)
推荐 转载

历史上的今天

在LOFTER的更多文章

评论

<#--最新日志,群博日志--> <#--推荐日志--> <#--引用记录--> <#--博主推荐--> <#--随机阅读--> <#--首页推荐--> <#--历史上的今天--> <#--被推荐日志--> <#--上一篇,下一篇--> <#-- 热度 --> <#-- 网易新闻广告 --> <#--右边模块结构--> <#--评论模块结构--> <#--引用模块结构--> <#--博主发起的投票-->
 
 
 
 
 
 
 
 
 
 
 
 
 
 

页脚

网易公司版权所有 ©1997-2017