在Oracle中进行大小写不敏感的查询

在Oracle中，命令和对象名称都是大小写不敏感的，因为Oracle在处理语句时，将所有的名称和命令全部转化为大写。

但是对于字符串中的字符，无论是比较还是排序，都是大小写敏感的。这在Oracle是默认方式，但不是唯一的方式。

下面看一个简单的例子：

SQL> CREATE TABLE T (NAME VARCHAR2(30));

表已创建。

SQL> INSERT INTO T VALUES ('A');

已创建1行。

SQL> INSERT INTO T VALUES ('a');

已创建1行。

SQL> INSERT INTO T VALUES ('B');

已创建1行。

SQL> COMMIT;

提交完成。

SQL> CREATE INDEX IND_T_NAME ON T(NAME);

索引已创建。

看一下默认情况下的排序和查询结果：

SQL> SELECT * FROM T ORDER BY NAME;

NAME
Cwn#~g,q0------------------------------
RB,o;G$mT'a0A
%Ce~?5yLe hmq`0B

AN d&Au eA0a

SQL> SELECT * FROM T WHERE NAME = 'A';

NAMEITPUB个人空间?W i%?Y|
------------------------------
wL[7c~a1c0A

这是最正常不过的结果了，下面修改会话默认的排序方式：

SQL> ALTER SESSION SET NLS_SORT = BINARY_CI;

会话已更改。

SQL> SELECT * FROM T ORDER BY NAME;

NAMEITPUB个人空间]R S%g}
------------------------------

]-PXi c$Z(O7m0A
y[U4O5oAGHp0a
(^y3L;Y?0B

SQL> SELECT * FROM T WHERE NAME = 'A';

NAME
3i$rv+hx0m B0------------------------------
v1r,}i$Kg5Y
_;Y0A

可以看到，通过设置排序方法为BINARY_CI，已经实现了对排序的大小写不敏感，但是查询语句中仍然是大小写敏感的，下面进一步修改比较方式：

SQL> ALTER SESSION SET NLS_COMP = LINGUISTIC;

会话已更改。

SQL> SELECT * FROM T ORDER BY NAME;

NAME
!jgv.h;J w3r%b0------------------------------ITPUB个人空间\jA@:xXp
A
v$_9LDp*P
P#J0aITPUB个人空间TWJr[$~X]G
B

SQL> SELECT * FROM T WHERE NAME = 'A';

NAMEITPUB个人空间p*j0l"xN[]f
------------------------------ITPUB个人空间^3UC-UuW Q3{j@
AITPUB个人空间1hUc:i.u*[I#B
a

现在已经达到了大小写不敏感查询的目的了，这是由于设置比较方式是基于语义的，而不是基于二进制的，而语言方式下A和a是没有区别的。

虽然目的达到了，但是还是要说明一下，这里虽然实现了对大小写不敏感的查询，但是这个结果的实现与表面看到的现象并不完全相同。

从查询语句上看，似乎只是对NAME进行一下判断就可以了，并未对列进行任何的操作，而实际上并非如此，下面看看这种情况下的执行计划：

SQL> SET AUTOT ON EXP
1fD'u h?0SQL> SELECT * FROM T WHERE NAME = 'A';

NAME
({8[)X eS0------------------------------ITPUB个人空间np7h*SW
A
WO+Wu9EqK0a

ITPUB个人空间7fG AY;{~-d or{ee
执行计划ITPUB个人空间"dms sL+BK
----------------------------------------------------------ITPUB个人空间^Rp:nOQCV ^u
Plan hash value: 1601196873

--------------------------------------------------------------------------
e Q!Y'PEK;?0| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
I` Vreut$}/s4F\o0--------------------------------------------------------------------------

p x5z9lW-LL0W0|   0 | SELECT STATEMENT  |      |     1 |    17 |     3   (0)| 00:00:01 |ITPUB个人空间(X&M2x?T2Ru
|*  1 |  TABLE ACCESS FULL| T    |     1 |    17 |     3   (0)| 00:00:01 |ITPUB个人空间Lvj j4D [Q:c y
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
%N*~3YB(x4u/b/mI'|0---------------------------------------------------

   1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100')ITPUB个人空间'Liq!f I9D7L} \u
              )

Note
v[`i$CZP,H0^0-----

h0h&Hk m0   - dynamic sampling used for this statement

Oracle居然对列进行了操作，将NAME进行了NLSSORT操作，然后判断是否与目标值进行判断。不过Oracle也没有其他的好方法进行处理，对等号右边的常量进行转换固然代价较低，但是SQL的判断条件就由等于变成了IN，这种转换恐怕变化更大。而且还要找到所有其他所有可能转换为目标值的常量，这个操作要比对列进行转换复杂得多。

不过这种方法就存在一个问题，就是Oracle无法使用索引了，一方面是由于对列进行了操作，另一方面是由于Oracle的索引是按照BINARY方式编码存储的。因此这种查询会采用全表扫描的方式。

SQL> SELECT /*+ INDEX(T IND_T_NAME) */ * FROM T WHERE NAME = 'A';

NAME
6K7]s$Ye!b0------------------------------ITPUB个人空间R? i0S$G8Xe
AITPUB个人空间Z&Bu6L+UzM/Y1K6R
a

ITPUB个人空间QHe rz
Yr
执行计划
5q PVl!i]7x0----------------------------------------------------------
1B FYtI;S*e0Plan hash value: 1601196873

--------------------------------------------------------------------------ITPUB个人空间is!VQ%~}:}`
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
k2S&I RfCF0--------------------------------------------------------------------------
rs|-Q8aT&Y?0|   0 | SELECT STATEMENT  |      |     1 |    17 |     3   (0)| 00:00:01 |ITPUB个人空间9Q6zd8v!ZvZTk
|*  1 |  TABLE ACCESS FULL| T    |     1 |    17 |     3   (0)| 00:00:01 |
}
z
s$gx?N!jU0--------------------------------------------------------------------------

Predicate Information (identified by operation id):
9})j%gy-AN0---------------------------------------------------

   1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100')ITPUB个人空间Q KE0q8G
              )

NoteITPUB个人空间2Rv0o&pC E
-----
]^$v/d'ae KG@0   - dynamic sampling used for this statement

这个情况，可以考虑建立一个函数索引来解决问题：

SQL> CREATE INDEX IND_T_L_NAME ON T(NLSSORT(NAME, 'NLS_SORT=BINARY_CI'));

索引已创建。

SQL> SELECT * FROM T WHERE NAME = 'A';

NAMEITPUB个人空间cj_4Vlse)}
------------------------------
o7U9~%\@YWG0AITPUB个人空间9C,I3c3_2hS6D]
a

K$fq1o{i\
q,}-i0执行计划ITPUB个人空间J'V@)lYW
----------------------------------------------------------
2J vDA7Aq0Plan hash value: 242883967

--------------------------------------------------------------------------------------------ITPUB个人空间dB8^kPC~/g
| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
MfPB-EDo0--------------------------------------------------------------------------------------------
*t X&yM.ZP9s0|   0 | SELECT STATEMENT            |              |     1 |    17 |     2   (0)| 00:00:01 |ITPUB个人空间)I"h`FMG \
|   1 |  TABLE ACCESS BY INDEX ROWID| T            |     1 |    17 |     2   (0)| 00:00:01 |
;UTZ
YX7SL0|*  2 |   INDEX RANGE SCAN          | IND_T_L_NAME |     1 |       |     1   (0)| 00:00:01 |
H,P(E;k6B[0--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
}*u8mb&K0---------------------------------------------------

   2 - access(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100') )

Note

c6Eo9}8_O0-----
0s`
in1LaL0   - dynamic sampling used for this statement

当然使用一些非大众化的功能就容易碰到bug，比如下面的例子：http://yangtingkun.itpub.net/post/468/460325