ORACLE数据库中,NLS_SORT和NLS_COMP参数控制着字符比较和排序的行为,如果配置不当,可能会使已有索引失去作用,从而影响数据库的性能。
首先,我们来看一下,可以通过哪些方式修改这两个参数。通过ALTER SESSION当然是没有问题了,那么通过环境变量是否可以那?实验如下:
[oracle@oadata ~]$ set | grep NLS
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'
[oracle@oadata ~]$
[oracle@oadata ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 9月 18 16:59:31 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select parameter,value from nls_session_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ¥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_SORT BINARY
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY ¥
NLS_COMP BINARY
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
已选择17行。
SQL>
SQL> exit
从 Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options 断开
[oracle@oadata ~]$ vi .bash_profile
[oracle@oadata ~]$ . .bash_profile
[oracle@oadata ~]$ set | grep NLS
NLS_COMP=LINGUISTIC
NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
NLS_LANG='SIMPLIFIED CHINESE_CHINA.AL32UTF8'
NLS_SORT=SCHINESE_PINYIN_M
[oracle@oadata ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on 星期三 9月 18 17:00:37 2013
Copyright (c) 1982, 2011, Oracle. All rights reserved.
连接到:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> select parameter,value from nls_session_parameters;
PARAMETER VALUE
------------------------------ ------------------------------
NLS_LANGUAGE SIMPLIFIED CHINESE
NLS_TERRITORY CHINA
NLS_CURRENCY ¥
NLS_ISO_CURRENCY CHINA
NLS_NUMERIC_CHARACTERS .,
NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT YYYY-MM-DD HH24:MI:SS
NLS_DATE_LANGUAGE SIMPLIFIED CHINESE
NLS_SORT SCHINESE_PINYIN_M
NLS_TIME_FORMAT HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT DD-MON-RR HH.MI.SSXFF AM
PARAMETER VALUE
------------------------------ ------------------------------
NLS_TIME_TZ_FORMAT HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY ¥
NLS_COMP LINGUISTIC
NLS_LENGTH_SEMANTICS BYTE
NLS_NCHAR_CONV_EXCP FALSE
已选择17行。
SQL>
由此可见,是可以通过环境变量来配置NLS_SORT 和NLS_COMP的(仅限于在session级别)。