`

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

OracleSQL.netAccessBlog 
阅读更多

项目已经开发完毕，由于业务变动，某些查询需要忽略大小写,研究是否在不修改原来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

1
顶
2
踩
分享到：
| NLS_SORT设置
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

    如何：让Oracle表及字段显示为区分大小写

    虽然在Oracle中默认采用全部大写的形式可以简化查询过程，但对于那些希望保留标识符原始大小写状态的开发者来说，通过使用PowerDesigner或其他方法来创建表和字段，可以使Oracle中的表及字段显示为区分大小写的形式...

    Oracle SQL高级编程(资深Oracle专家力作，OakTable团队推荐)--随书源代码

    他认为对于SQL的学习是永无止境的，相信每一个查询Oracle数据库的人都需要精通SQL语言，才能写出高效的查询。他参与本书的编写就是为了帮助别人实现这一目标。 目录 封面 -11 封底 -10 扉页 -9 版权 -8 版权声明 -7...

    解决oracle 10g以SYS身份登录失败的方法

    Oracle数据库的密码区分大小写，且长度有限制。如果你忘记了密码，可以使用数据库管理员的特权重置密码。通常，这需要访问数据库的控制文件，使用特定的SQL语句来更改SYS用户的密码。 #### 使用正确的身份验证模式 ...

    Oracle10g数据库DBA2官方资料OCP

    - **大小写和重音差异处理**：使用 `NLS_COMP` 和 `NLS_SORT` 参数来控制字符串比较时是否忽略大小写和重音差异。 - **获取全球化配置信息**：可以使用 `DBMS_GLOBALIZATION.get_parameter_value()` 函数获取当前的...

    数据库迁移方案

    ### 数据库迁移方案 #### Oracle数据库升级经验总结与迁移指南 在进行Oracle数据库的升级或迁移过程中，确保数据完整性和业务连续性至关重要。本文将基于提供的文件内容，深入解析Oracle数据库迁移过程中的关键...

    两个版本Mysql和Oracle的分页标签

    然而，这种方法在大数据量分页时可能会有性能问题，因为`OFFSET`会扫描并忽略很多不需要的行。为了避免这种情况，可以使用`ROW_NUMBER()`函数结合子查询来提高效率： ```sql SELECT * FROM ( SELECT *, (@rownum:=...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    其一、就业面广：全球前100强企业99家都在使用ORACLE相关技术，中国政府机构，大中型企事业单位都能有ORACLE技术的工程师岗位。 其二、技术层次深：如果期望进入IT服务或者产品公司（类似毕博、DELL、IBM等），...

    Oracle DBA 应知应会 - RAID 和 Oracle数据库.docx

    此外，由于RAID 5在写操作时需要进行校验计算，这可能会对数据库的写性能造成负面影响，尤其是当数据块大小较大时。 TEMP表空间通常用于临时对象，如排序和联接操作，它也需要高性能的随机读写。因此，RAID 5的性能...

    oracle导入导出DMP数据库

    Oracle 导入导出 DMP 数据库 Oracle 导入导出 DMP 数据库是指使用 Oracle 数据库管理系统提供的导入导出工具，实现数据库的导入和导出。这里我们将详细介绍两种方法：使用客户端 Enterprise Manager Console 和使用...

    Oracle9i的init.ora参数中文说明

    Oracle9i初始化参数中文说明 Blank_trimming: 说明: 如果值为TRUE, 即使源长度比目标长度 (SQL92 兼容) 更长, 也允许分配数据。 值范围: TRUE | FALSE 默认值: FALSE serializable: 说明: 确定查询是否获取表级...

    oracle sqluldr2 包含linux、windows 32 64位客户端，在测试环境下真实测试通过，并写有详细的说明

    2. **外部表**：SQL*Loader也可以利用Oracle的外部表功能，将数据文件视为数据库中的虚拟表，从而直接进行查询和加载。 3. **参数文件**：在运行SQL*Loader时，用户可以指定包含各种选项的参数文件，如数据文件的...

    Oracle海量数据库系统的优化策略.pdf

    Oracle数据库作为当前最为广泛使用的数据库系统之一，在处理海量数据时必须采用一系列优化策略来提升性能和确保系统的稳定性。 首先，数据库的硬件环境优化是至关重要的。这里的硬件环境主要包括CPU、内存、存储...

    redhatserver5+oracle9+roseha

    例如，如果忽略了一些必要的目录权限设置，可能会导致Oracle数据库无法正常启动或运行。 - 此外，在配置iSCSI连接时，如果使用系统自带的工具可能遇到兼容性问题，建议手动安装和配置iSCSI客户端。 - 在创建用户和组...

    Oracle10g2ForLinux创建数据库实例过程借鉴.pdf

    在Oracle数据库管理中，创建一个新的数据库实例是一项关键任务，尤其对于Linux环境下的Oracle 10g版本。以下是一个详细的步骤指南，介绍了如何在Linux上创建Oracle 10g 2数据库实例。 首先，确保你已经以`oracle`...

    BIEE11G通过外部表做权限认证

    进行SQL查询时，通常会用到“upper”函数来忽略大小写，这是因为不同操作系统中，用户输入的用户名和密码可能存在大小写不一致的情况。在编写SQL查询语句时，应确保其能正确匹配到数据库中的记录。 当初始化块设置...

    ExamDiff文件比较工具

    用户可以选择忽略大小写、空格、行尾字符等差异，也可以自定义规则以适应特定的需求。 在界面设计上，ExamDiff提供了两种视图模式：并排视图和合并视图。并排视图将两个文件或目录的差异以左右对照的方式展示，而...

    linxu 常用命令

    - `grep -i "pattern" filename` - 忽略大小写进行搜索。 8. **find** - 搜索文件系统中的文件。 - `find /path -name "filename"` - 在指定路径下查找名为 filename 的文件。 - `find . -type f -mtime +30` - ...

    内存映射文件

    - **同步问题**：虽然内存映射简化了并发访问的同步，但并不意味着可以完全忽略，特别是在多个进程修改同一文件时，仍需注意适当的同步机制。 - **异常处理**：程序在映射文件后，必须正确地处理异常情况，例如...

    数据库的导入与导出方法

    ### 数据库的导入与导出方法详解 ...此外，随着技术的发展，现在也有更多现代化的工具和技术可以用来实现数据库的备份和迁移，如Oracle GoldenGate、Oracle Data Pump等，它们提供了更高效、更安全的解决方案。

Magicbox
Global site tag (gtag.js) - Google Analytics