在Oracle中进行大小写不敏感的查询
来源:互联网 发布:linux ctrl alt 编辑:程序博客网 时间:2024/05/17 20:59
在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
------------------------------
A
B
a
SQL> SELECT * FROM T WHERE NAME = 'A';
NAME
------------------------------
A
这是最正常不过的结果了,下面修改会话默认的排序方式:
SQL> ALTER SESSION SET NLS_SORT = BINARY_CI;
会话已更改。
SQL> SELECT * FROM T ORDER BY NAME;
NAME
------------------------------
A
a
B
SQL> SELECT * FROM T WHERE NAME = 'A';
NAME
------------------------------
A
可以看到,通过设置排序方法为BINARY_CI,已经实现了对排序的大小写不敏感,但是查询语句中仍然是大小写敏感的,下面进一步修改比较方式:
SQL> ALTER SESSION SET NLS_COMP = LINGUISTIC;
会话已更改。
SQL> SELECT * FROM T ORDER BY NAME;
NAME
------------------------------
A
a
B
SQL> SELECT * FROM T WHERE NAME = 'A';
NAME
------------------------------
A
a
现在已经达到了大小写不敏感查询的目的了,这是由于设置比较方式是基于语义的,而不是基于二进制的,而语言方式下A和a是没有区别的。
虽然目的达到了,但是还是要说明一下,这里虽然实现了对大小写不敏感的查询,但是这个结果的实现与表面看到的现象并不完全相同。
从查询语句上看,似乎只是对NAME进行一下判断就可以了,并未对列进行任何的操作,而实际上并非如此,下面看看这种情况下的执行计划:
SQL> SET AUTOT ON EXP
SQL> SELECT * FROM T WHERE NAME = 'A';
NAME
------------------------------
A
a
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100')
)
Note
-----
- 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
------------------------------
A
a
执行计划
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T | 1 | 17 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100')
)
Note
-----
- 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';
NAME
------------------------------
A
a
执行计划
----------------------------------------------------------
Plan hash value: 242883967
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 17 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IND_T_L_NAME | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('6100') )
Note
-----
- dynamic sampling used for this statement
- 在Oracle中进行大小写不敏感的查询
- 在Oracle中进行大小写不敏感的查询
- 在Oracle中进行大小写不敏感的查询
- 在Oracle中进行大小写不敏感的查询
- 在Oracle中进行大小写不敏感的查询
- 在Oracle中进行大小写不敏感的查询
- 在Oracle中进行大小写不敏感的查询
- Oracle大小写不敏感的查询
- 大小写不敏感的查询条件处理
- mysql查询对大小写不敏感的问题
- 对oracle大小写敏感的查询处理方式
- 大小写不敏感的字符串
- Oracle中处理不区分大小写查询的处理
- Oracle中处理不区分大小写查询的处理
- Oracle中处理不区分大小写查询的处理 .
- MySQL查询字符串时大小写不敏感
- Mysql查询大小写不敏感简便解决办法
- mysql 查询字符串大小写不敏感
- JDK1.5新特性介绍
- Switch
- HGE 系列教材(8) --- hgeResourceManager helper class(本文未完成)
- 【摘录】用来装载Moduel到正在运行的Kernel和移除Moduel的用户空间工具
- Some topics on designing C++ class
- 在Oracle中进行大小写不敏感的查询
- HGE 系列教材(9) --- GUI(本文未完成)
- FIRST_ROWS优化模式语言排序模糊匹配问题
- 测试sql数据库连接代码
- 不同服务器的session访问
- 使用Java实现在SQLserver中实现图片的存储
- oracle中用户连接相关
- 陈一舟:绝对不卖校内网[转]
- 索引的选择性