NLS_SORT和NLS_COMP的设置影响性能

author：skate
time：2010/03/04

---

NLS_SORT和NLS_COMP的设置影响性能

 

 

通过设置这两个参数可以实现自定义的排序和比较的方式，虽然对写程序有优点，但他也会带来负面的影响
就是导致sql不走索引，而走全表扫描，当表的数据量很大是，非常影响性能

 

NLS_SORT和NLS_COMP是10g的新特性，大家在使用新特性的不要只考虑其优点，也要注意它带来的负面影响

 

NLS_SORT：
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.

 
Modifiable：  ALTER SESSION SET NLS_SORT =''
Range of values：  BINARY or any valid linguistic definition name
 

 

NLS_COMP ：
specifies the collation behavior of the database session.

 

Values: { BINARY | LINGUISTIC | ANSI }

 

 

 

不同值说明

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 whichyou want linguistic comparisons.

 

ANSI
A setting of ANSI is for backwards compatibility; in general, you should set NLS_COMP to LINGUISTIC

 

 

 

参考文档：

 

http://www.hellodba.com/Cases/Improper_NLS_Parameter_case.htm

 

 

 

---end---