oracle 查询时忽略大小写的方案研究

最新推荐文章于 2023-06-01 10:18:09 发布
最新推荐文章于 2023-06-01 10:18:09 发布
阅读量594 收藏
点赞数
分类专栏： oracle 管理 文章标签： Oracle SQL .net Access Blog

项目已经开发完毕，由于业务变动，某些查询需要忽略大小写,研究是否在不修改原来SQL语句的基础上，通过修改数据库参数满足业务。

实验如下：

SYS@huiche>conn /@huiche as sysdba;
已连接。

假如项目中有一张表 t：
SYS@huiche>create table t (name varchar2(10));

表已创建。

SYS@huiche>insert into t values ('test');

已创建 1 行。

SYS@huiche>insert into t values ('TEST');

已创建 1 行。

SYS@huiche>begin
  2  for i in 1..1000 loop
  3   insert into t values ('hello');
  4  end loop;
  5  end;
  6  /

PL/SQL 过程已成功完成。

SYS@huiche>commit;

提交完成。

SYS@huiche>begin
  2  dbms_stats.gather_table_stats(user,'t');
  3  end;
  4  /

PL/SQL 过程已成功完成。

SYS@huiche>set autotrace on;

假设业务SQL语句执行如下：
SYS@huiche>select * from t where name like 't%';

NAME
----------------------------------------
test


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   334 |  1670 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   334 |  1670 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("NAME" LIKE 't%')


统计信息
----------------------------------------------------------
        135  recursive calls
          0  db block gets
         18  consistent gets
          0  physical reads
          0  redo size
        417  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          1  rows processed

SYS@huiche>create index t_name_index on t(name);

索引已创建。

创建索引以后走的是索引扫描。

SYS@huiche>select * from t where name like 't%';

NAME
----------------------------------------
test


执行计划
----------------------------------------------------------
Plan hash value: 837813613

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |   334 |  1670 |     2   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_NAME_INDEX |   334 |  1670 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - access("NAME" LIKE 't%')
       filter("NAME" LIKE 't%')


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

这张表的另一个业务SQL如下：

SYS@huiche>select * from t where name='test';

NAME
----------------------------------------
test


执行计划
----------------------------------------------------------
Plan hash value: 837813613

---------------------------------------------------------------------------------
| Id  | Operation        | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT |              |   334 |  1670 |     1   (0)| 00:00:01 |
|*  1 |  INDEX RANGE SCAN| T_NAME_INDEX |   334 |  1670 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------

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

   1 - access("NAME"='test')


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

现在为了忽略大小写，而修改SESSION级别的参数

SYS@huiche>alter session set nls_sort='BINARY_CI';

会话已更改。

SYS@huiche>alter session set nls_comp='LINGUISTIC';

会话已更改。

原来like的查询继续走的索引扫描(不过走的是索引全扫描)

SYS@huiche>select * from t where name like 't%';

NAME
----------------------------------------
TEST
test


执行计划
----------------------------------------------------------
Plan hash value: 3593352198

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |   334 |  1670 |     2   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T_NAME_INDEX |   334 |  1670 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("NAME" LIKE 't%')


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          7  consistent gets
          2  physical reads
          0  redo size
        458  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

原来的等于查询执行计划变成了全表扫描

SYS@huiche>select * from t where name='test';

NAME
----------------------------------------
test
TEST


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   334 |  1670 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T    |   334 |  1670 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter(NLSSORT("NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('7465737
              400') )


统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          5  consistent gets
          0  physical reads
          0  redo size
        458  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

创建函数索引：
SYS@huiche>create index t_name_function_index on t(NLSSORT("NAME",'nls_sort=''BINARY_CI'''));

索引已创建。

like查询走的还是全索引扫描

SYS@huiche>select * from t where name like 't%';

NAME
----------------------------------------
TEST
test


执行计划
----------------------------------------------------------
Plan hash value: 3593352198

-------------------------------------------------------------------------------------
| Id  | Operation            | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |              |   334 |  1670 |     2   (0)| 00:00:01 |
|*  1 |  INDEX FAST FULL SCAN| T_NAME_INDEX |   334 |  1670 |     2   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

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

   1 - filter("NAME" LIKE 't%')


统计信息
----------------------------------------------------------
          8  recursive calls
          0  db block gets
          9  consistent gets
          0  physical reads
          0  redo size
        458  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

相等查询走的是INDEX RANGE SCAN 扫描

SYS@huiche>select * from t where name='test';

NAME
----------------------------------------
test
TEST


执行计划
----------------------------------------------------------
Plan hash value: 4037064198

-----------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                       |    10 |    50 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T                     |    10 |    50 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | T_NAME_FUNCTION_INDEX |     4 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

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


统计信息
----------------------------------------------------------
         31  recursive calls
          0  db block gets
          9  consistent gets
          1  physical reads
          0  redo size
        458  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          2  rows processed

假设还有另一张表：业务是SQL语句根据主键查询，实验由于忽略大小写而影响其执行计划：

SYS@huiche>create table test (id number);

表已创建。

SYS@huiche>begin
  2  for i in 1..10000 loop
  3  insert into test values (i);
  4  end loop;
  5  end;
  6  /

PL/SQL 过程已成功完成。

SYS@huiche>commit;

提交完成。

SYS@huiche>create unique index test_id_index on test(id);

索引已创建。

SYS@huiche>select * from test where id=1;

        ID
----------
         1


执行计划
----------------------------------------------------------
Plan hash value: 3821046056

-----------------------------------------------------------------------------------
| Id  | Operation         | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |               |     1 |    13 |     1   (0)| 00:00:01 |
|*  1 |  INDEX UNIQUE SCAN| TEST_ID_INDEX |     1 |    13 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - access("ID"=1)


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

SYS@huiche>

看来其执行计划没有受到影响。

从以上实验可以看出，设置忽略大小写的参数以后，oracle 将根据语意来进行查询和排序，对有的执行计划是有一定的影响的，通过建立函数索引，可以使查询走向正确的执行计划。

假如在某些业务中又想使用区分大小写的查询怎么办？

可以用函数解决：

例如：

想查询t表中name为test的记录:

SYS@huiche>select * from t where nlssort(name,'nls_sort=''BINARY''')=nlssort('test','nls_sort=''BINARY''');

NAME
------------------------------
test


执行计划
----------------------------------------------------------
Plan hash value: 1601196873

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

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

   1 - filter("NAME"='test')

Note
-----
   - dynamic sampling used for this statement


统计信息
----------------------------------------------------------
         19  recursive calls
          0  db block gets
         20  consistent gets
          0  physical reads
          0  redo size
        411  bytes sent via SQL*Net to client
        385  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL>

以上查询，虽然查出结果，但是走的是全表扫描。

或者更好的方式是修改应用，等每次和数据库建立连接的时候动态设置session级别nls_sort和nls_comp的值,这样就解决了忽略大小写与不忽略大小写同时存在的问题。

思考中，未完待续。

请参考：http://dbua.iteye.com/blog/845245

lb517290079
关注
专栏目录
【Oracle】Oracle查所有表和视图忽略大小写
tttzzzqqq2018的博客
12-12 479
【代码】【Oracle】Oracle查所有表和视图。
mysql查询忽略大小写，已整理成文档
目标检测专栏持续更新中，改进YOLO系列通用，适用v5、v7、v8、所有博客均是团队原创博客，所有文章禁止转载，违者必究。
07-21 201
梳理知识点，是快速提升技术的关键 前面讲过，快速提升自己的技术硬实力其实是有方法的。大致就是梳理知识点+夯实基础+进阶深入学习+实战，下面我会一点点跟大家剖析，本文干货满满，大家仔细阅读。 ①梳理知识必备：互联网主流技术地图 ②梳理知识后，夯实基础乃是刚需 明确说明一下，从互联网招聘需求来看，Java基础扎实一定是招聘方的基本要求，也对自己以后的学习有一定影响。 ③深入进阶学习（28个主流Java知识点“一网打尽”） 1、并发编程 Java并发编程是整个Java开发体系中最难以理解，但也是最
oracle 查询忽略大小写
北京堵车王的专栏
09-24 198
oracle 查询忽略大小写 数据库有4 条记录,分别是 'KKK'  'kkk'   'KkK'  'kKk'(1)select * from t_vvip t  where t.name=lower('KkK') or t.name=Upper('KkK');     仅转换了查询的字符串, 即查询的内容要么全部小写,要么全部大写。所以只能查到2条记录       (2)   select...
Oracle查询忽略大小写的实现方法
iteye_1760的博客
12-20 2794
Oracle查询忽略大小写的实现方法 2010-10-27 17:16 佚名 互联网 字号：T | T Oracle数据库中进行查询时，由于数据有大小写，可能会对查询结果造成麻烦，下面就教您一个忽略大小写进行查询的方法，供您参考。 AD：2013云计算架构师峰会课程资料下载   Oracle查询有时候要受到一些条件的制约，比如大小写。下面为您介绍了...
oracle查询字段不区分大小写
wy970629的博客
06-01 2666
我们都知道oracle数据库是区分大小写的，但是有些需求为了提高用户使用体验，不希望让它区分大小写，那么我们就要在sql上用到“REGEXP_LIKE(字符串,正则表达式,'i')”，当参数设置为“i”时，说明进行匹配不区分大小写。这个是在当前会话上，也就是说在每次发起查询的时候都需要执行这个才能起作用的，REGEXP_LIKE和模糊查询LIKE有一样的使用效果！- 'n'， 允许使用可以匹配任意字符的操作符（通常是'.'）- 'c'， 说明在进行匹配时区分大小写（默认选项）
如何：让Oracle表及字段显示为区分大小写
12-21
虽然在Oracle中默认采用全部大写的形式可以简化查询过程，但对于那些希望保留标识符原始大小写状态的开发者来说，通过使用PowerDesigner或其他方法来创建表和字段，可以使Oracle中的表及字段显示为区分大小写的形式...
记——数据库大小写敏感情况下兼容系统中原有的sql语句
qq_34332035的博客
01-05 1181
1、问题 环境中MySQL数据库需要改成大小写敏感形式，而系统中存在着以前留下来的老sql，格式不规范，数目庞大，使用多种jdbc框架，如hibernate，jdbctemplate等；需要老sql的表名全部改为大写 2、mysql 大小写策略说明 my.cnf里将lower_case_table_names=1 表示对表名大写不敏感； MySQL在Linux下数据库名、表名...
Oracle 查询技巧与优化（三） 字符串操作
小灯光环
08-31 6290
关于Oracle查询优化的相关字符串操作以及常用的字符串函数~
oracle递归查询出现死循环,一次马失前蹄的SQL优化：递归查询引发的血案
weixin_31439907的博客
04-03 1191
原标题：一次马失前蹄的SQL优化：递归查询引发的血案作者介绍黄浩：从业十年，始终专注于SQL。十年一剑，十年磨砺。3年通信行业，写就近3万条SQL；5年制造行业，遨游在ETL的浪潮；2年性能优化，厚积薄发自成一家。在分享时，有读者表示“很想知道，作者失败的时候是怎么办？”，并且看热闹不嫌事大，要求“来一篇文章呗”。好吧，正所谓，常在河边走，哪有不湿鞋。本人在SQL优化领域摸爬滚打多年，“接客”无数...
oracle 模糊查询忽略大小写
weixin_33670713的博客
12-03 1223
select * from c_table t where upper(t.name) like '%F%';--将字段全转成大写 select * from c_table t where lower(t.name) like '%f%';--将字段全转成小写 select * from c_table t where t.name like upper('%f%') or t.name l...
Oracle/查询时忽略大小写
流光岁月的专栏
10-12 6257
在这里详述 Oracle/查询时忽略大小写。 Oracle查询有时候要受到一些条件的制约，比如大小写。下面为您介绍了一个实现Oracle查询忽略大小写的方法，如果您对Oracle查询方面感兴趣的话，不妨一看。 数据库有4 条记录,分别是 'KKK' 'kkk'   'K
oracle函数忽略大小写,Oracle中不区分大小写的search
weixin_29190573的博客
04-04 1223
在不使用全文索引的情况下，有三种主要的方法可以在Oracle中执行不区分大小写的search。最终你select什么方法取决于你的个人情况。 要记住的主要是要提高性能，您必须正确索引不区分大小写的search。1.一致地列出你的专栏和你的string。您可以使用UPPER()或LOWER()强制所有的数据是相同的情况：select * from my_table where upper(colum...
oracle、MySQL SQL查询忽略参数大小写
weixin_45213302的博客
03-30 2955
适用数据库 目前我只在MySQL 和Oracle 使用过。 我是先准备了一个测试表 测试表SQL这里是用的MySQL导出的，大家可以自己创建一个表进行测试： /* Navicat Premium Data Transfer Source Server : mysql Source Server Type : MySQL Source Server Version : 80025 Source Host : localhost:3306 Sou
oracle函数忽略大小写,Oracle中不区分大小写的主键
weixin_30311179的博客
04-04 748
我们的数据的语义不区分大小写，因此我们将oracle会话配置为不区分大小写：alter session set NLS_COMP=LINGUISTIC;alter session set NLS_SORT=BINARY_AI;然后，为了利用索引，我们还希望主键也不区分大小写：create table SCHEMA_PROPERTY (NAME nvarchar2(64) not null,V...
Oracle 查询不区分大小写 （正则函数）
热门推荐
huaishuming的专栏
05-28 1万+
//不区分大小写查询  REGEXP_LIKE(字段名, '(" + keyword+ ")', 'i') " ); Oracle使用正则表达式离不开这4个函数： 1。regexp_like 2。regexp_substr 3。regexp_instr 4。regexp_replace 看函数名称大概就能猜到有什么用了。 regexp_like 只能用于条件表达式，和
Oracle 实现查询不区分大小写(设置数据库)
无名指的约定
07-08 5067
查询数据的时候。SQL Server 默认 不区分大小写。 如果要区分，就要额外的设置。oracle 默认 区分大小写。如果不要区分，就要额外的设置。下面就是 Oracle 的明细部分了。首先是测试表/数据 SQL> CREATE TABLE tab ( id INT,val VARCHAR2(10)); 表已创建。 SQL> INSERT INTO tab values (1, 'ABC123'),(2, 'abc234'),(3, 'Abc345
oracle忽略大小写_Oracle存储大纲Outline总结
weixin_42502288的博客
01-23 326
概述在实际的工作当中，可能会碰到在开发环境或测试环境中系统运行正常，但是移植到真实生产环境中却发现响应速度很慢，如果在调试之后效果仍然不佳的情况下，可以尝试使用outlines来稳定个别SQL语句的执行计划。概念Oracle Outline，中文也称为存储大纲，是最早的基于提示来控制SQL执行计划的机制，也是9i以及之前版本唯一可以用来稳定和控制SQL执行计划的工具。outline是一个hints...
ORACLE数据库配置忽略大小写
最新发布
06-03
如果要忽略大小写，可以通过修改 NLS 参数来实现。具体步骤如下： 1. 登录数据库，执行以下命令查看当前 NLS 参数的设置： ``` SELECT * FROM nls_database_parameters WHERE parameter LIKE '%NLS%'; ``` 2. ...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

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

抵扣说明：

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

余额充值