oracle百万级数据排序sql,oracle sql 排序与比较中的技巧与注意事项（一）

oracle sql 排序与比较中的技巧与注意事项(一)

发布时间：2020-06-14 11:48:05

来源：51CTO

阅读：481

作者：安静随风

在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 排序。