oracle查询不区分大小写的设置

有时候我们查询的时候不想通过lower或者upper等函数而把大小写的内容都查询出来,在oracle10g可以这么实现。[@more@]
有时候我们查询的时候不想通过lower或者upper等函数而把大小写的内容都查询出来,在oracle10g可以这么实现。

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> select * from test;

ID
--------------------
A
a

SQL> select * from test where id='a';

ID
--------------------
a

SQL> ALTER SESSION SET NLS_COMP=ANSI;

Session altered

SQL> ALTER SESSION SET NLS_SORT=binary_ci;

Session altered

SQL> select * from test where id='A';

ID
--------------------
A
a

SQL> select * from test where id='a';

ID
--------------------
A
a

SQL>

10gR2中,nls_com新增加了一个值LINGUISTIC ,设置这个值,可以使在nl_sort中设置大小写不敏感。相应的,nl_sort也增加了一个值:BINARY_CI,(CI即Case Insensitive),也就是大小写不敏感。

但是,实际上设置过这两个值以后,并非真正大小写不敏感了,而是相当于Oracle会自动给语句加上upper函数。看以下例子:


SQL> set autot on
SQL> select * from t2 where f1 = 'a';

F1 AAA
---------- ----------
a 2


Execution Plan
----------------------------------------------------------
Plan hash value: 2238318762

--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | SELECT STATEMENT | | 1 | 5 | 2 (0)| 00:
00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 5 | 2 (0)| 00:
00:01 |
|* 2 | INDEX RANGE SCAN | T2_IDX1 | 1 | | 1 (0)| 00:
00:01 |
--------------------------------------------------------------------------------
-------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - access("F1"='a')


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed

SQL> alter session set NLS_SORT=BINARY_CI;

Session altered.

SQL> alter session set NLS_COMP=LINGUISTIC;

Session altered.

SQL> select * from t2 where f1 = 'a';

F1 AAA
---------- ----------
A 1
a 2


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 5 | 3 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| T2 | 1 | 5 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(NLSSORT("F1",'nls_sort=''BINARY_CI''')=HEXTORAW('6100') )


Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
0 bytes sent via SQL*Net to client
0 bytes received via SQL*Net from client
0 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed

zhouwf0726 发表于:2007.03.14 14:30 ::分类: ( oracle开发 ) ::阅读:(171次) :: 评论 (2) :: 引用 (0)
comment_icon1.gif re: 设置oracle查询不区分大小写 [回复]

zhouweifeng老大,通过那两个例子是怎么看出ORACLE自动添加了UPPER哪????

love2008 评论于:2007.03.15 09:17
comment_icon1.gif re: 设置oracle查询不区分大小写 [回复]

本文的后边是我在整理文章时转载了一部分,估计但是的作者也是通过看执行计划的full table scan这么定论的,其实不是很确切。oracle通过hextoraw函数把表中字段内容和参数值转换为16进制来进行比较的:
SQL> insert into test values(hextoraw('a'));

1 row inserted

SQL> select * from test;

ID
--------------------
0A

SQL> insert into test values(hextoraw('A'));

insert into test values(hextoraw('A'))

ORA-00001: 违反唯一约束条件 (ROME.PK_TEST)

SQL> insert into test values('a');

1 row inserted

SQL> select * from test;

ID
--------------------
0A
a

SQL> insert into test values('A');

1 row inserted

SQL> select * from test;

ID
--------------------
0A
A
a

SQL> select * from test where id='a';

ID
--------------------
a

SQL> select * from test where id=hextoraw('a');

ID
--------------------
0A

SQL> select * from test where hextoraw(id)=hextoraw('a');

ID
--------------------
0A
A
a

SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/34329/viewspace-910909/,如需转载,请注明出处,否则将追究法律责任。

转载于:http://blog.itpub.net/34329/viewspace-910909/

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值