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

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

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

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

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

抵扣说明:

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

余额充值