多語言、語系資料的儲存滿足了多種語系系統的需求。在之前的《nls_sort與漢字排序》（http://space.itpub.net/17203031/viewspace-690198）中，我們介紹瞭如何在Oracle中透過初始化引數，來控制不同語言型別的排序方式。本篇作為續篇，繼續介紹在使用nls_sort控制引數時，應該注意的一些問題。

在nls_sort引數官方介紹中，有如下一段話：

“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.”

具體含義是：當nls_sort引數設定除binary外的其他值時，會引起在排序中發生全表掃描，無論最佳化器選擇何種方式。binary取值方式下，這種情況是不會發生的，因為索引預設的構建方式也是按照binary方式。如果設定進行任何的語義排序，最佳化器必須進行一個全表掃描。

這段話說明了使用nls_sort引數時需要注意的一個大問題，就是原有索引執行路徑的固定。下面我們來透過一個實驗來解釋現象。

實驗環境準備

//構建資料表

SQL> create table t (id number, enchar varchar2(10), chchar varchar2(10));

Table created

SQL> desc t;

Name Type Nullable Default Comments

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

ID NUMBER Y

ENCHAR VARCHAR2(10) Y

CHCHAR VARCHAR2(10)

SQL> select * from t;

ID ENCHAR CHCHAR

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

42 dlfs 實驗

43 lwe 哦咦餓死

44 slsd 搜查

45 olsf 了科技

46 ojols 冷漠

47 ojols 獨立

48 ojols 了科技是

49 lils 沒咯

8 rows selected

//當前系統引數環境（預設狀態）

SQL> show parameter nls

NAME TYPE VALUE

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

nls_comp string

nls_iso_currency string

nls_language string SIMPLIFIED CHINESE

nls_sort string

（篇幅原因，有刪節…）

//構建普通索引

SQL> create unique index idx_t_ch on t(chchar);

Index created

我們構建了包含漢字的實驗環境資料表T。欄位chchar上構建了唯一索引，結合該欄位非空屬性，索引樹idx_t_ch上包括了chchar列上所有的取值。

查詢實驗

進行一般方式查詢。

SQL> explain plan for select * from t order by chchar;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 2227664955

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

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

| 0 | SELECT STATEMENT | | 8 | 112 | 2 (0)| 00

| 1 | TABLE ACCESS BY INDEX ROWID| T | 8 | 112 | 2 (0)| 00

| 2 | INDEX FULL SCAN | IDX_T_CH | 8 | | 1 (0)| 00

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

9 rows selected

該語句中沒有where條件，但是Oracle CBO最佳化器還是選擇了索引路徑。執行路徑是什麼呢？對索引idx_t_ch進行全葉節點掃描（index full scan），獲取到對應所有資料行的rowid。注意：因為索引葉節點排序順序是binary，與預設的nls_sort方式相同，所以葉節點返回rowid順序就是資料集合rowid順序。之後，根據這個rowid列表訪問資料行（Table Access by index rowid）。這個過程中沒有發生常見發生的SORT操作。

當我們希望轉換一種排序方式時，執行計劃發生什麼變化呢？

//轉換為筆畫排序

SQL> alter session set nls_sort='SCHINESE_RADICAL_M';

Session altered

SQL> alter system flush shared_pool;

System altered

SQL> explain plan for select * from t order by chchar;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 961378228

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

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

| 0 | SELECT STATEMENT | | 8 | 112 | 3 (34)| 00:00:01 |

| 1 | SORT ORDER BY | | 8 | 112 | 3 (34)| 00:00:01 |

| 2 | TABLE ACCESS FULL| T | 8 | 112 | 2 (0)| 00:00:01 |

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

9 rows selected

此處，Oracle最佳化器在進行非binary取值nls_sort排序的時候，忽略了chchar列上的索引。進行全表掃描之後，將資料集合進行SORT排序操作。相同的資料統計量、相同的SQL語句，但是Oracle選擇了不同的執行路徑計劃。

那麼，如何解釋Oracle這種現象呢？還是要從CBO最佳化器的立足點出發。之前筆者文章中，一直在強調兩個觀點：首先，索引不是免費的午餐。使用索引執行計劃是有成本付出的。其次，就是在CBO時代，索引只是為CBO最佳化器提供了一種備選方案。至於說CBO最後的決斷，我們說正常情況下，還是一個綜合成本的考量。

在第一種情況下（nls_sort=binary），Oracle選擇進行索引全掃描，這樣付出一個額外的索引葉節點掃描成本。但是收益顯著，索引樹葉節點列鍵值排序順序也是按照binary方式，那麼獲取到的rowid列表順序，天生就是最後order by的順序。Oracle只要按照rowid集合的順序，訪問資料表對應記錄，這樣獲取的資料集合直接就是order by 的結果。所以，我們在執行計劃中，看不到sort操作。

第二種情況下（nls_sort=’ SCHINESE_RADICAL_M’）,Oracle如果選擇索引路徑，在付出讀索引塊成本的情況下，獲取到的rowid集合順序是按照binary方式排序的。在訪問資料表塊的時候，還是要付出sort成本。這樣，還不如直接進行全表掃描，之後老老實實的sort。於是乎，Oracle選擇了全表掃描策略。

解釋了原因，我們引申一步。我們一般的系統索引排序都是按照binary方式的。如果突然透過alter session，甚至修改引數檔案改變的order by的依據，那麼也就意味著絕大多數包括order by子句的SQL語句都將失去索引路徑這個最佳化方案。所以，對於nls_sort引數，我們的選擇原則一定是慎重和小範圍。

那麼，是不是使用非binary的nls_sort方式，我們就沒有方法藉助索引了呢？倒也不是。我們可以藉助語系索引的方式，對一些特殊業務場景構建索引。請參加下面程式碼示例：

//刪除原有的索引

SQL> drop index idx_t_ch;

Index dropped

SQL> create unique index idx_t_chliq on t(nlssort(chchar, 'nls_sort=''SCHINESE_RADICAL_M'''));

Index created

SQL> exec dbms_stats.gather_table_stats(user,'T',cascade => true);

PL/SQL procedure successfully completed

SQL> alter system flush shared_pool;

System altered

SQL> alter session set nls_sort='SCHINESE_RADICAL_M';

Session altered

SQL> explain plan for select * from t order by chchar;

Explained

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT

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

Plan hash value: 3701718915

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

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

| 0 | SELECT STATEMENT | | 8 | 128 | 2 (0)|

| 1 | TABLE ACCESS BY INDEX ROWID| T | 8 | 128 | 2 (0)|

| 2 | INDEX FULL SCAN | IDX_T_CHLIQ | 8 | | 1 (0)|

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

9 rows selected

我們重新組織了索引結構，索引執行計劃就恢復回來。重點在於我們使用了語系索引，構建結構如下：

create index index_name on tab_name(nlssort(column_name, 'nls_sort=''binary_ci'''));

語系索引本質上是一種類似函式索引的方法。透過nlssort函式，強行指定使用索引排序方式。這樣，在構建的索引葉節點順序就是按照語系所要求的順序進行了。

由此，我們得到如下結論：

1、如果沒有顯著的需求壓力，一般不要選擇使用大可見性範圍nls_sort非binary排序方案。binary方案無論是從全域性相容性，還是從處理效率上，都有很強的優勢。特別是一些移植系統或者生產系統，nls_sort的選擇一定要慎重；

2、如果有對於非binary排序方式的要求，請儘量控制範圍在session甚至更小的SQL級別。不要提升nls_sort作用的範圍；

3、對一些非binary排序的業務場景，開發團隊一定要給予充分的關注和支援。及時對一些已有的索引結構進行更新；

語系排序nls_sort與語系索引Linguistic Index

相關文章