在sql排序中，oracle默認(rèn)采用二進(jìn)制的排序方法。大小寫有不同的值，大寫的值排在前面。有時候，我們需要處理的情況是，希望忽略大小寫來進(jìn)行排序。有多種方法可以實現(xiàn)：
設(shè)置NLS環(huán)境變量
alter session set NLS_SORT = 'BINARY_CI';
使用UPPER和LOWER函數(shù)
用UPPER函數(shù)和LOWER函數(shù)把要比較的字段名、文字都轉(zhuǎn)換成大寫或者小寫后再比較。這種方法的不足之處在于，使用函數(shù)后，標(biāo)準(zhǔn)的索引就不能再使用了，優(yōu)化器無法正常工作，應(yīng)對的方式是使用基于功能的索引(function-based index)。
注意:NLS_SORT僅僅影響排序的結(jié)果，并不對其他大小寫操作造成影響。若要解決不區(qū)分大小寫的比較操作，我們同樣可以采用設(shè)置NLS環(huán)境變量的方式來完成:
alter session set NLS_COMP = 'LINGUISTIC';
官方文檔中關(guān)于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.
根據(jù)上文中標(biāo)紅部分的注視，如果NLS_SORT不是設(shè)置為"Binary"，那么就會引起全表掃描，是不會使用索引的，在我們的系統(tǒng)中變更單涉及到的數(shù)據(jù)都是數(shù)據(jù)龐大的表，如果不使用到索引，查詢的效率會受到影響。
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.
根據(jù)標(biāo)紅的部分，要提高性能可以在需要比較的列上建立一個linguistic index。若想使NLS_COMP參數(shù)值為LINGUISTIC生效，需要設(shè)置NLS_SORT為LINGUISTIC 排序。
另外有需要云服務(wù)器可以了解下創(chuàng)新互聯(lián)cdcxhl.cn，海內(nèi)外云服務(wù)器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務(wù)器、裸金屬服務(wù)器、高防服務(wù)器、香港服務(wù)器、美國服務(wù)器、虛擬主機(jī)、免備案服務(wù)器”等云主機(jī)租用服務(wù)以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務(wù)可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應(yīng)用場景需求。
重慶分公司
重慶分公司
