189 8069 5689
在sql排序中，oracle默认采用二进制的排序方法。大小写有不同的值，大写的值排在前面。有时候，我们需要处理的情况是，希望忽略大小写来进行排序。有多种方法可以实现：
设置NLS环境变量
alter session set NLS_SORT = 'BINARY_CI';
使用UPPER和LOWER函数
用UPPER函数和LOWER函数把要比较的字段名、文字都转换成大写或者小写后再比较。这种方法的不足之处在于，使用函数后，标准的索引就不能再使用了，优化器无法正常工作，应对的方式是使用基于功能的索引(function-based index)。
注意:NLS_SORT仅仅影响排序的结果，并不对其他大小写操作造成影响。若要解决不区分大小写的比较操作，我们同样可以采用设置NLS环境变量的方式来完成:
alter session set NLS_COMP = 'LINGUISTIC';
官方文档中关于NLS_SORT和NLS_COMP有这样一段话:
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.
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.
根据上文中标红部分的注视，如果NLS_SORT不是设置为"Binary"，那么就会引起全表扫描，是不会使用索引的，在我们的系统中变更单涉及到的数据都是数据庞大的表，如果不使用到索引，查询的效率会受到影响。
NLS_COMP specifies the collation behavior of the database session.
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.
根据标红的部分，要提高性能可以在需要比较的列上建立一个linguistic index。若想使NLS_COMP参数值为LINGUISTIC生效，需要设置NLS_SORT为LINGUISTIC 排序。
另外有需要云服务器可以了解下创新互联cdcxhl.cn，海内外云服务器15元起步，三天无理由+7*72小时售后在线，公司持有idc许可证，提供“云服务器、裸金属服务器、高防服务器、香港服务器、美国服务器、虚拟主机、免备案服务器”等云主机租用服务以及企业上云的综合解决方案，具有“安全稳定、简单易用、服务可用性高、性价比高”等特点与优势，专为企业上云打造定制，能够满足用户丰富、多元化的应用场景需求。