`

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
评论
发表评论

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

相关推荐

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