多语言、语系数据的保存满足了多种语系系统的需求。在之前的《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/