nls_sort與漢字排序

 

朋友在進行報表需求評估的時候，突然問筆者一個問題：在資料庫裡面，漢字進行排序的順序是按照什麼方法？對空值null，是排在第一位還是最後一位？當時做了一個簡單的小實驗，立刻獲得了答案。但是之後，在想是不是還有其它方式進行排序的支援呢？這時可以使用nls_sort引數設定。

 

 

nls_sort是Oracle的初始化引數之一。根據官方文件的介紹，該引數“specifies the collating sequence for Order by queries”，也就是說，設定該引數是可以控制SQL order by語句顯示結果順序的依據的。

 

 

初始化環境

 

首先，構建實驗環境和資料。

 

//10gR2資料庫環境

SQL> select * from v$version;

 

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0    Production

 

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

 

//資料表T，其中包括數字和字元型別欄位

SQL> desc t;

Name   Type         Nullable Default Comments

------ ------------ -------- ------- --------

IDNUM  NUMBER(10)   Y                        

ENCHAR VARCHAR2(10) Y                        

CHCHAR VARCHAR2(10) Y                        

 

//包括中文、英文和空行；

SQL> select * from t;

 

      IDNUM ENCHAR     CHCHAR

----------- ---------- ----------

          1 adf        中國

          2 flsd      

          3 sd         阿姨

          4 lk         實驗

          5            罪惡

          6 mll        饕餮

         21 lf         lfs

 

7 rows selected

 

 

 

nls_sort相關引數情況，發現nls_sort引數的設定是空。

 

 

SQL> show parameter nls

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------

nls_calendar                         string     

nls_comp                             string     

（篇幅原因，省略…）

nls_language                         string      SIMPLIFIED CHINESE

nls_sort                             string     

 

 

nls_sort取值分為兩種型別：binary或者linguistic_definition。binary表示排序標準按照字元儲存的順序進行儲存，這種方式對DBMS進行處理來說是最快的一種排序方式。linguistic_definition是指語系定義，通常一種語言language要對應特定的一系列語系定義。

 

當前系統中，我們發現nls_sort引數並沒有進行定義。在沒有定義nls_sort的時候，系統採用的排序方式與nls_language所對應的預設排序Default Sort方式相同。

 

在Oracle文件庫的《Globalization Support Guide》中，定義了各種language對應的Default Sort方式。當前nls_language取值為“SIMPLIFIED CHINESE”，對應的預設排序方式就是binary。

 

預設排序方式

 

在當前系統中，採用binary作為排序方式，對應的是儲存的二進位制編碼順序。下面透過實驗來證明。

 

SQL> col idnum for a5;

SQL> select t.*, dump(chchar,1016) from t order by chchar;

 

IDNUM ENCHAR     CHCHAR     DUMP(CHCHAR,1016)

----- ---------- ---------- --------------------------------------------------------------------------------

   21 lf         lfs        Typ=1 Len=3 CharacterSet=ZHS16GBK: 6c,66,73

    3 sd         阿姨       Typ=1 Len=4 CharacterSet=ZHS16GBK: b0,a2,d2,cc

    4 lk         實驗       Typ=1 Len=4 CharacterSet=ZHS16GBK: ca,b5,d1,e9

    1 adf        中國       Typ=1 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa

    5            罪惡       Typ=1 Len=4 CharacterSet=ZHS16GBK: d7,ef,b6,f1

    6 mll        饕餮       Typ=1 Len=4 CharacterSet=ZHS16GBK: f7,d2,f7,d1

    2 flsd                  NULL

 

7 rows selected

 

 

透過dump成16進位制的字元編碼，可以看出binary排序方式。而且，在binary方式下，空會被放在最末尾位置。

 

 

中文可用排序方式

 

從Oracle9i開始，對中文語言方式（Simplified Chinese和Traditional Chinese）提供了多種排序方式。

 

 

由此，就為中文進行排序提供了多種的依據和設定。

 

 

設定排序方法1——引數檔案設定

 

這種方法是最根本的方法。在引數檔案pfile中，設定引數nls_sort的取值。之後重新啟動資料庫例項即可。這種方法會應用排序方式到例項所有的查詢中，實際中較少使用。

 

設定排序方法2——Session會話級別設定

 

可以在會話中，透過alter session設定nls_sort取值。之後該會話就會自動應用上新的nls_sort方式。

 

SQL> alter session set nls_sort='SCHINESE_RADICAL_M';

 

Session altered

 

SQL> select t.*, dump(chchar,1016) from t order by chchar;

 

IDNUM ENCHAR     CHCHAR     DUMP(CHCHAR,1016)

----- ---------- ---------- --------------------------------------------------------------------------------

   21 lf         lfs        Typ=1 Len=3 CharacterSet=ZHS16GBK: 6c,66,73

    1 adf        中國       Typ=1 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa

    4 lk         實驗       Typ=1 Len=4 CharacterSet=ZHS16GBK: ca,b5,d1,e9

    5            罪惡       Typ=1 Len=4 CharacterSet=ZHS16GBK: d7,ef,b6,f1

    3 sd         阿姨       Typ=1 Len=4 CharacterSet=ZHS16GBK: b0,a2,d2,cc

    6 mll        饕餮       Typ=1 Len=4 CharacterSet=ZHS16GBK: f7,d2,f7,d1

    2 flsd                  NULL

 

7 rows selected

 

SQL> alter session set nls_sort='SCHINESE_STROKE_M';

 

Session altered

 

SQL> select t.*, dump(chchar,1016) from t order by chchar;

 

IDNUM ENCHAR     CHCHAR     DUMP(CHCHAR,1016)

----- ---------- ---------- --------------------------------------------------------------------------------

   21 lf         lfs        Typ=1 Len=3 CharacterSet=ZHS16GBK: 6c,66,73

    1 adf        中國       Typ=1 Len=4 CharacterSet=ZHS16GBK: d6,d0,b9,fa

    3 sd         阿姨       Typ=1 Len=4 CharacterSet=ZHS16GBK: b0,a2,d2,cc

    4 lk         實驗       Typ=1 Len=4 CharacterSet=ZHS16GBK: ca,b5,d1,e9

    5            罪惡       Typ=1 Len=4 CharacterSet=ZHS16GBK: d7,ef,b6,f1

    6 mll        饕餮       Typ=1 Len=4 CharacterSet=ZHS16GBK: f7,d2,f7,d1

    2 flsd                  NULL

 

7 rows selected

 

 

透過session級別的設定。我們可以清晰看出不同排序方法的效果。

 

 

設定排序方法3——在order by子句中直接指定

 

除了上述的兩種方法，還可以在select語句的order by子句中直接指定排序方式。這樣可以在SQL語句級別設定排序方式，應該是一種最靈活的控制方式。

 

 

SQL> select * from t order by chchar;

 

      IDNUM ENCHAR     CHCHAR

----------- ---------- ----------

         21 lf         lfs

          3 sd         阿姨

          4 lk         實驗

          1 adf        中國

          5            罪惡

          6 mll        饕餮

          2 flsd      

 

7 rows selected

 

SQL> select * from t order by nlssort(chchar,'nls_sort=SCHINESE_RADICAL_M');

 

      IDNUM ENCHAR     CHCHAR

----------- ---------- ----------

         21 lf         lfs

          1 adf        中國

          4 lk         實驗

          5            罪惡

          3 sd         阿姨

          6 mll        饕餮

          2 flsd      

 

7 rows selected

 

SQL> select * from t order by nlssort(chchar,'nls_sort=SCHINESE_STROKE_M');

 

      IDNUM ENCHAR     CHCHAR

----------- ---------- ----------

         21 lf         lfs

          1 adf        中國

          3 sd         阿姨

          4 lk         實驗

          5            罪惡

          6 mll        饕餮

          2 flsd      

 

7 rows selected

 

 

 

此外，對很多的資料訪問介面，如JDBC、Hibernate和JPA，都在一定程度上支援多種排序方式的指定。具體的方式可以參見相關文獻資料，本文就不加以累述了。

 

 

最後，作為本文的結束，也作為一個新問題的起點。使用nls_sort之後，雖然可以方便的指定排序方式，但是還會帶來一些問題。其中最大的問題就是索引的失效性。nls_sort的改寫改變了原有的索引結構和順序，會導致原有的索引不能使用。從而影響整體系統效能。

 

在這種情況下，就需要設定一種較為特殊的索引index型別，語系linguistic Indexes，同時也要對nls_comp引數加以控制設定，這樣可以保證索引的正確設定和提高系統訪問效率。這個問題，留待我們以後繼續進行討論。