`

NLS_COMP和NLS_SORT

 
阅读更多

oracle中gbk和utf8的数据库在默认nls_comp、nl_sort默认都是binary，对大小写敏感。

数据库是GBK的情况

当设置nls_somp = LINGUISTIC or ANSI 且 nls_sort= binary_ci的情况大小写不敏感，其他均大小写敏感。

数据库为UTF8的是情况

当设置nls_somp = LINGUISTIC or ANSI 且nls_sort= languagename_ci的情况大小写不敏感，nls_sort= languagname的时候大小写敏感。

  

===========================================================

select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');

select * from NLS_DATABASE_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');

select * from NLS_Instance_Parameters where parameter in ('NLS_COMP','NLS_SORT');

 

ALTER SESSION SET NLS_COMP=LINGUISTIC;

ALTER SESSION SET NLS_SORT=BINARY_CI;

 

create table cw(name varchar2(20));
 insert into cw values('IPTV');
 insert into cw values('iptv');
 insert into cw values('Iptv');

 

select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw;

select SYS_CONTEXT('USERENV','CURRENT_SCHEMA') CURRENT_SCHEMA from dual

 

 ============================================================================

nls_comp

nls_comp的值影响sql的比较行为。

oracle允许在各个字符集上进行基于语言的比较，排序操作，正常做法是通过系统函数实现，比较麻烦，可以通过设置nls_comp为LINGUISTIC，再结合nls_sort简化。

nls_comp 默认是BINARY，允许范围：BINARY , LINGUISTIC, or ANSI

当设置LINGUISTIC时需要设置NLS_SORT才能生效，ANSI就是LINGUISTIC的兼容参数，正常应该使用LINGUISTIC。

可以通过ALTER SESSION SET NLS_COMP = LINGUISTIC; 设置

一旦把nls_comp设置为LINGUISTIC需要根据nls_sort这是的情况重新创建索引，否则会照成索引失效。

CREATE INDEX i ON t(NLSSORT(col, 'NLS_SORT=FRENCH'));

 

The value of NLS_COMP affects the comparison behavior of SQL operations.

You can use NLS_COMP to avoid the cumbersome process of using the NLSSORT function in SQL statements when you want to perform a linguistic comparison instead of a binary comparison. When NLS_COMP is set to LINGUISTIC, SQL operations perform a linguistic comparison based on the value of NLS_SORT. A setting of ANSI is for backward compatibility; in general, you should set NLS_COMP to LINGUISTIC when you want to perform a linguistic comparison.

nls_sort

The NLS_SORT option specifies the sequence of character values used when sorting or comparing text. The value of NLS_SORT affects the GT,GE, LT, and LE operators, SORT command, and the SORTLINES function.

Within a session, you can dynamically modify the value of this option using the OLAP DML syntax show below or by using the SQL statementALTER SESSION SET option = value.  

nls_sort选项指明在所文本排序或者比较的时候character的值得顺序，它会影响到gt，ge，lt and le 操作，以及sort命令和sortlines 函数。这里没有明确说是否影响等于操作符。

 

 

字符集与nls_comp\nls_sort

通过实际验证gbk和utf8的数据库在默认nls_comp、nl_sort默认都是binary，对大小写敏感。

数据库是GBK的情况

当设置nls_somp = LINGUISTIC or ANSI 切 nls_sort= binary_ci的情况大小写不敏感，其他均大小写敏感。

数据库为UTF8的是情况

当设置nls_somp = LINGUISTIC or ANSI 切 nls_sort= languagename_ci的情况大小写不敏感，nls_sort= languagename的时候大小写敏感。

 

GBK数据库

SQL>  conn test/test@192.168.105.63:1521/orcl

Connected.

SQL>  select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

 

PARAMETER                                                                                           VALUE

------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

NLS_CHARACTERSET                                                                                     ZHS16GBK

 

SQL> select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');

 

PARAMETER                                                                                                                                                                                                    VALUE

------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

NLS_SORT                                                                                                                                                                                                        BINARY

NLS_COMP                                                                                                                                                                                                      BINARY

 

SQL> ALTER SESSION SET NLS_COMP=LINGUISTIC;

ALTER SESSION SET NLS_SORT=BINARY_CI;

Session altered.

 

SQL>

 

Session altered.

 

SQL> select * from test.cw ;    

 

NAME

------------------------------------------------------------

IPTV

iptv

Iptv

 

SQL> select * from test.cw where name = 'iptv';

 

NAME

------------------------------------------------------------

IPTV

iptv

Iptv

 

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw;  2 

 

  CASEWHEN

----------

          1

          1

          1

 

SQL> ALTER SESSION SET NLS_SORT=BINARY;

 

Session altered.

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw  2  ;

 

  CASEWHEN

----------

          1

          2

          3

 

SQL>  select * from test.cw where name = 'iptv';

 

NAME

------------------------------------------------------------

iptv

 

SQL>  ALTER SESSION SET NLS_SORT='XSPANISH';

 

Session altered.

 

SQL>  select * from test.cw where name = 'iptv';

 

NAME

------------------------------------------------------------

iptv

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw  2  ;

 

  CASEWHEN

----------

          1

          2

          3

 

SQL>  ALTER SESSION SET NLS_SORT='XSPANISH_CI';

 

Session altered.

 

SQL> select * from test.cw where name = 'iptv';

 

NAME

------------------------------------------------------------

iptv

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw  2  ;

 

  CASEWHEN

----------

          1

          2

          3

 

SQL>  ALTER SESSION SET NLS_SORT='ITALIAN';

 

Session altered.

 

SQL> select * from test.cw where name = 'iptv';

 

NAME

------------------------------------------------------------

iptv

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw  2  ;

 

  CASEWHEN

----------

          1

          2

          3

 

SQL> ALTER SESSION SET NLS_SORT='ITALIAN_CI';

 

Session altered.

 

SQL> select * from test.cw where name = 'iptv';

 

NAME

------------------------------------------------------------

iptv

 

SQL>  select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw  2  ;

 

  CASEWHEN

----------

          1

          2

          3

 

SQL>

UTF8数据库

SQL> conn sys/oracle@192.168.105.63:1521/oral2 as sysdba

Connected.

SQL>  set line 2000

SQL>  select * from nls_database_parameters where parameter='NLS_CHARACTERSET';

 

PARAMETER                     VALUE

------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------

NLS_CHARACTERSET              AL32UTF8

 

SQL> select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');

 

PARAMETER                                                                                                                              VALUE

------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------

NLS_SORT                                                                                                                                  BINARY

NLS_COMP                                                                                                                                BINARY

 

SQL> select * from sys.cw ;           

 

NAME

--------------------

iptv

IPTV

Iptv

 

SQL> select * from sys.cw where name = 'iptv';

 

NAME

--------------------

iptv

 

SQL>  select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw;

  2 

  CASEWHEN

----------

          2

          1

          3

 

SQL>  ALTER SESSION SET NLS_COMP=LINGUISTIC;

 

Session altered.

 

SQL> ALTER SESSION SET NLS_SORT=BINARY_CI;

 

Session altered.

 

SQL> select * from sys.cw where name = 'iptv';

 

NAME

--------------------

iptv

IPTV

Iptv

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw;  2 

 

  CASEWHEN

----------

          1

          1

          1

 

SQL>  ALTER SESSION SET NLS_SORT='XSPANISH';

 

Session altered.

 

SQL> select * from sys.cw where name = 'iptv';

 

NAME

--------------------

iptv

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw;  2 

 

  CASEWHEN

----------

          2

          1

          3

 

SQL> ALTER SESSION SET NLS_SORT='XSPANISH_CI';

 

Session altered.

 

SQL> select * from sys.cw where name = 'iptv';

 

NAME

--------------------

iptv

IPTV

Iptv

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw;  2 

 

  CASEWHEN

----------

          1

          1

          1

 

SQL> ALTER SESSION SET NLS_SORT='ITALIAN';

 

Session altered.

 

SQL>  select * from sys.cw where name = 'iptv';

 

NAME

--------------------

iptv

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw;  2 

 

  CASEWHEN

----------

          2

          1

          3

 

SQL>  ALTER SESSION SET NLS_SORT='ITALIAN_CI';

 

Session altered.

 

SQL>  select * from sys.cw where name = 'iptv';

 

NAME

--------------------

iptv

IPTV

Iptv

 

SQL> select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw;  2 

 

  CASEWHEN

----------

          1

          1

          1

 

SQL> select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');

 

PARAMETER                                                                                                                              VALUE

------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------

NLS_SORT                                                                                                                                  ITALIAN_CI

NLS_COMP                                                                                                                                LINGUISTIC

 

SQL> ALTER SESSION SET NLS_COMP=BINARY

  2  ;

 

Session altered.

 

SQL>  select * from sys.cw where name = 'iptv';

 

NAME

--------------------

iptv

 

SQL>  select case when name = 'IPTV' then 1 when name = 'iptv' then 2 when name = 'Iptv' then 3 end casewhen

from cw;  2 

 

  CASEWHEN

----------

          2

          1

          3

 

SQL>  select * from NLS_SESSION_PARAMETERS where parameter in ('NLS_COMP','NLS_SORT');

 

PARAMETER                                                                                                                              VALUE

------------------------------------------------------------------------------------------------------------------------ ----------------------------------------------------------------------------------------------------------------------------------------------------------------

NLS_SORT                                                                                                                                  ITALIAN_CI

NLS_COMP      

附录

NLS_SORT

The NLS_SORT option specifies the sequence of character values used when sorting or comparing text. The value of NLS_SORT affects the GT,GE, LT, and LE operators, SORT command, and the SORTLINES function.

Within a session, you can dynamically modify the value of this option using the OLAP DML syntax show below or by using the SQL statementALTER SESSION SET option = value.  

Data Type

TEXT

Syntax

NLS_SORT = option-value

Arguments

See Oracle Database Globalization Support Guide for more information about the NLS_SORT parameter.

Examples

Example 6-78 Binary and Linguistic Sorts

A dimension named words has the following values.

cerveza, Colorado, cheremoya, llama, luna, lago

This example shows the results of a binary sort.

NLS_SORT = 'BINARY'
SORT words A words
STATUS words
The current status of WORDS is:
Colorado, cerveza, cheremoya, lago, llama, luna

A Spanish language sort results in this order.

NLS_SORT = 'SPANISH'
SORT words A words
STATUS words
The current status of WORDS is:
cerveza, cheremoya, Colorado, lago, llama, luna

An extended Spanish language sort results in this order.

NLS_SORT = 'XSPANISH'
SORT words A words
STATUS words
The current status of WORDS is:
cerveza TO cheremoya, lago TO llama

Previous Page

Page 128 of 604

 

NLS_SORT specifies the collating sequence for ORDER BY queries.

  • 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.

 

The value of NLS_COMP affects the comparison behavior of SQL operations.

You can use NLS_COMP to avoid the cumbersome process of using the NLSSORT function in SQL statements when you want to perform a linguistic comparison instead of a binary comparison. When NLS_COMP is set to LINGUISTIC, SQL operations perform a linguistic comparison based on the value of NLS_SORT. A setting of ANSI is for backward compatibility; in general, you should set NLS_COMP to LINGUISTIC when you want to perform a linguistic comparison.

Set NLS_COMP to LINGUISTIC as follows:

ALTER SESSION SET NLS_COMP = LINGUISTIC;
 

When NLS_COMP is set to LINGUISTIC, a linguistic index improves the performance of the linguistic comparison. To enable a linguistic index, use the following syntax:

CREATE INDEX i ON t(NLSSORT(col, 'NLS_SORT=FRENCH'));

NLS_SORT

http://docs.oracle.com/cd/B28359_01/olap.111/b28126/dml_options073.htm

http://docs.oracle.com/cd/B28359_01/server.111/b28320/initparams145.htm

NLS_COMP

http://docs.oracle.com/cd/B28359_01/server.111/b28298/ch3globenv.htm#NLSPG231

 

 

 

 

分享到：
| oracle的collation
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    ora-01460 错误原因分析

    | NLS_SORT | BINARY | | NLS_TIME_FORMAT | HH.MI.SSXFFAM | | NLS_TIMESTAMP_FMT | DD-MON-RRHH.MI.SSXFFAM | | NLS_TIME_TZ_FMT | HH.MI.SSXFFAMTZR | | NLS_TS_TZ_FMT | DD-MON-RRHH.MI.SSXFFAMTZR | | NLS_DUAL...

    Oracle客户端PL/SQL 中文乱码解决

    可以通过`ALTER SESSION SET NLS_SORT=BINARY_CI`和`ALTER SESSION SET NLS_COMP=LINGUISTIC`来调整排序规则，使比较时不考虑字符集差异。 通过以上步骤，大部分情况下可以解决Oracle客户端PL/SQL的中文乱码问题。...

    Oracle10g数据库DBA2官方资料OCP

    - **大小写和重音差异处理**：使用 `NLS_COMP` 和 `NLS_SORT` 参数来控制字符串比较时是否忽略大小写和重音差异。 - **获取全球化配置信息**：可以使用 `DBMS_GLOBALIZATION.get_parameter_value()` 函数获取当前的...

    oracle详解

    将sort_area_size设置为一个较大的值,比如100M 2.避免日志切换等待 增加重做日志组的数量,增大日志文件大小. 3.优化日志缓冲区 比如将log_buffer容量扩大10倍(最大不要超过5M) 4.使用阵列插入与提交 commit = y 注意...

    PHP 代码自动生成工具 e-World Tech PHPMaker 2020.0.3 英文特别免费版.rar

    Oracle sort – 用于设置Oracle的NLS_SORT参数 自动填充原始值 – 对于自动填充原始（数据库）值而不是查找值 多文件上载分隔符 – 指定用于分隔文件名的文件上载分隔符 将Colorbox用于图像 使用响应式布局 使用移动...

    Oracle9i的init.ora参数中文说明

    可以使用 NLS_COMP 指定必须根据NLS_SORT 会话参数进行语言比较。 值范围: Oracle8i National Language Support Guide 中指定的任何有效的10 字节字符串。 默认值: BINARY nls_currency: 说明: 为 L 数字格式...

    Oracle定义联合数组及使用技巧

    在全局化设置的环境中，如NLS_COMP或NLS_SORT参数影响下，使用字符串作为索引时需要额外的处理。 联合数组是FORALL语句和BULK COLLECT子句的重要组成部分，这些特性支持批量操作，提高处理效率。例如，BULK COLLECT...

    c语言数据结构算法演示(Windows版)

    递归工作栈中含调用语句行号 adr、变参 nls 和值参 ls。 15． 创建广义表的存储结构 图示窗口显示广义表存储结构的建立过程和算法执行过程中参数Sub的当前值。 16． 遍历二叉树 图示窗口显示二叉树的逻辑结构和遍历...

    用c描述的数据结构演示软件

    递归工作栈中含调用语句行号 adr、变参 nls 和值参 ls。 15． 创建广义表的存储结构 图示窗口显示广义表存储结构的建立过程和算法执行过程中参数Sub的当前值。 16． 遍历二叉树 图示窗口显示二叉树的逻辑结构和遍历...

    数据结构演示软件

    递归工作栈中含调用语句行号 adr、变参 nls 和值参 ls。 15． 创建广义表的存储结构 图示窗口显示广义表存储结构的建立过程和算法执行过程中参数Sub的当前值。 16． 遍历二叉树 图示窗口显示二叉树的逻辑结构和...

Magicbox
Global site tag (gtag.js) - Google Analytics