越南的一些应用,需要使用忽略大小写的操作。使排序以及查询数据时,符合越南本地的一些特殊要求。我们很容易猜测, 忽略大小写之后,如何使用索引一定会受到影响,而且约束也应该需要考虑这个问题。例如 查询a,A, 如何使用索引来查找符合这样要求的数据。如果1个会话使用语言学排序,另外一个使用正常的二进制内码排序,此时会怎样。由于这个问题实际是国际化支持手册涵盖的范围,在查阅Database Globalization Support Guide后,特此记录下来本次实验过程。
====================================================
问题:
1. 查找 =, instr ,like ...等操作时,忽略大小写
2. 排序时,忽略大小写
====================================================
解决方法:
ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=BINARY_CI;
考虑数据库其他应用, 使用会话级参数实现上述要求
同时考虑在使用语言学字符操作时与普通操作存在差异,所以普通的索引实际不满足上述要求
需要使用基于NLSSORT的索引方式
====================================================
测试验证
1.
CREATE TABLE t1 (c1 VARCHAR2(100));
INSERT INTO t1 VALUES('a');
INSERT INTO t1 VALUES('A');
INSERT INTO t1 VALUES('Z');
INSERT INTO t1 VALUES('D');
INSERT INTO t1 VALUES('z');
INSERT INTO t1 VALUES('d');
insert into t1 values('Mc Calla');
insert into t1 values('MCAfee');
insert into t1 values('McCoye');
insert into t1 values('Mccathye');
commit;
select * from t1 order by 1;
select * from t1 where c1='a';
select * from t1 where c1 like 'MC%';
ALTER SESSION SET NLS_COMP=LINGUISTIC;
ALTER SESSION SET NLS_SORT=BINARY_CI;
select * from t1 order by 1;
select * from t1 where c1='a';
select * from t1 where c1 like 'MC%';
2. 测试约束,索引等在修改参数后的
create unique index idx1_t1 on t1(c1);
create unique index idx2_t1 on t1(NLSSORT(c1,'NLS_SORT =BINARY_CI'));
create index idx3_t1 on t1(NLSSORT(c1,'NLS_SORT =BINARY_CI'));
explain plan for
select * from t1 where c1='a';
select * from table(dbms_xplan.display());
==========================================================================
仅存在普通方式建立的索引,在使用语言学排序等操作情况下
SQL> select * from table(dbms_xplan.display());
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3617692013
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T1 | 1 | 52 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
1 - filter(NLSSORT("C1",'nls_sort=''BINARY_CI''')=HEXTORAW('6100') )
Note
-----
- dynamic sampling used for this statement (level=2)
==========================================================================
仅有 普通方式建立的索引,在普通方式排序等操作情况下
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 348235116
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 52 | 0 (0)| 00:00:01 |
|* 1 | INDEX UNIQUE SCAN| IDX1_T1 | 1 | 52 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
1 - access("C1"='a')
==========================================================================
使用语言学中方式 建立的索引 idx3_t1,在使用语言学排序等操作情况下
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
Plan hash value: 1904793794
---------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 459 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T1 | 1 | 459 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX3_T1 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
2 - access(NLSSORT("C1",'nls_sort=''BINARY_CI''')=HEXTORAW('6100') )
Note
-----
- dynamic sampling used for this statement (level=2)
同时我们可以看到唯一约束,将受到语言学含义的影响。
同时我们在实验中如果仅仅设置ALTER SESSION SET NLS_SORT=BINARY_CI; 不设置NLS_COMP参数时,实际仅仅影响排序, 但= ,like操作等不受到语言学含义操作的影响。这也就意味着排序操作和谓词操作中实际可以有独立的语言学含义。