多语言、语系数据的保存满足了多种语系系统的需求。在之前的《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
-----------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
----------------------------------------------------------
| 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
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------
| 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
----------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 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博客 ” ，链接：http://blog.itpub.net/17203031/viewspace-691724/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/17203031/viewspace-691724/
298
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
