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