ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 语系排序nls_sort与语系索引Linguistic Index

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

原创 Linux操作系统 作者:realkid4 时间:2011-04-05 19:17:22 0 删除 编辑

 

多语言、语系数据的保存满足了多种语系系统的需求。在之前的《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/,如需转载,请注明出处,否则将追究法律责任。

上一篇: pck_nbs_runstats脚本
请登录后发表评论 登录
全部评论
求道~

注册时间:2010-11-30

  • 博文量
    545
  • 访问量
    7688264