oracle sql 排序與比較中的技巧與注意事項（一）

標籤：sort   nls_sort   nls_comp   

在sql排序中，oracle預設採用二進位的排序方法。大小寫有不同的值，大寫的值排在前面。有時候，我們需要處理的情況是，希望忽略大小寫來進行排序。有多種方法可以實現：

1. 設定NLS環境變數

   alter session set NLS_SORT = ‘BINARY_CI‘;

2. 使用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 排序。

本文出自 “夢的港灣” 部落格，請務必保留此出處http://9785919.blog.51cto.com/9775919/1811587

oracle sql 排序與比較中的技巧與注意事項（一）