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

官方文档中关于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 排序。

本文出自 “梦的港湾” 博客，请务必保留此出处http://9785919.blog.51cto.com/9775919/1811587

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

标签：sort   nls_sort   nls_comp