多语言、语系数据的保存满足了多种语系系统的需求。在之前的《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排序的业务场景，开发团队一定要给予充分的关注和支持。及时对一些已有的索引结构进行更新；

ITPub博客

语系排序nls_sort与语系索引Linguistic Index

最新文章