189 8069 5689
在sql排序中，oracle默认采用二进制的排序方法。大小写有不同的值，大写的值排在前面。有时候，我们需要处理的情况是，希望忽略大小写来进行排序。有多种方法可以实现：
成都创新互联专业为企业提供崇礼网站建设、崇礼做网站、崇礼网站设计、崇礼网站制作等企业网站建设、网页设计与制作、崇礼企业网站模板建站服务，10余年崇礼做网站经验，不只是建网站，更提供有价值的思路和整体网络服务。
设置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 排序。