Oracle模糊查询之（2.如何测试模糊查询的时间及使用是否使用索引）反向索引与模糊查询

来源：互联网发布：淘宝卖零食的店铺编辑：程序博客网时间：2024/04/20 09:06

反向索引与模糊查询

反向索引应用于前模糊的用法简介：

1、反向索引

SQL> create index ind_name on atest reverse(name);

SQL> select * from atest where name like reverse ('%y');

2、联合部分反向索引

SQL> create index ind_idname on atest (id ,reverse(name));

索引已创建。

SQL> select * from atest where id=1 and name like reverse ('%y');

未选定行

具体用法展示如下：

反向索引与模糊查询
---------------------

版本

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

测试数据
SQL> conn an/an
已连接。

SQL> create table atest(id int,name varchar2(100));
表已创建。

SQL> insert into atest select 1,'anbaisheng' from dual;
已创建 1 行。

SQL> insert into atest select 2,'xiangxiang' from dual;
已创建 1 行。

SQL> insert into atest select 3,'baby' from dual;
已创建 1 行。

SQL> commit;
提交完成。

SQL> select * from atest;
        ID NAME
---------- --------------------
         1 anbaisheng
         2 xiangxiang
         3 baby

创建正续索引
SQL> create index ind_name on atest(name);

索引已创建。

SQL> set autotrace on
SQL> select * from atest where name like 'a%';

        ID NAME
---------- --------------------
         1 anbaisheng

--------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    65 |     2   (0)| 00
|   1 | TABLE ACCESS BY INDEX ROWID| ATEST    |     1 |    65 |     2   (0)| 00
|* 2 |   INDEX RANGE SCAN          | IND_NAME |     1 |       |     1   (0)| 00
--------------------------------------------------------------------------------

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

   2 - access("NAME" LIKE 'a%')
       filter("NAME" LIKE 'a%')

SQL> select * from atest where name like '%y';

        ID NAME
---------- --------------------
         3 baby

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

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

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

正向索引后模糊查询有效，前模糊无效

创建反向索引
SQL> drop index ind_name;

索引已删除。

SQL> create index ind_name on atestreverse(name);

索引已创建。

SQL> select * from atest where name like '%y';

        ID NAME
---------- --------------------
         3 baby

SQL> select * from atest where name like reverse ('%y');

未选定行

--------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    65 |     0   (0)| 00
|   1 | TABLE ACCESS BY INDEX ROWID| ATEST    |     1 |    65 |     0   (0)| 00
|* 2 |   INDEX RANGE SCAN          | IND_NAME |     1 |       |     0   (0)| 00
--------------------------------------------------------------------------------

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

   2 - access("NAME" LIKE 'y%')
       filter("NAME" LIKE 'y%')

就算建上反向索引，不使用reverse关键字前模糊也没用，后模糊无效

联合部分反向索引
这个conception里都没说，自己试了下，还成
SQL> create index ind_idname on atest (id ,reverse(name));

索引已创建。

SQL> select * from atest where id=1 and name like reverse ('%y');

未选定行

--------------------------------------------------------------------------------
| Id | Operation                   | Name     | Rows | Bytes | Cost (%CPU)| Ti
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |     1 |    65 |     0   (0)| 00
|* 1 | TABLE ACCESS BY INDEX ROWID| ATEST    |     1 |    65 |     0   (0)| 00
|* 2 |   INDEX RANGE SCAN          | IND_NAME |     1 |       |     0   (0)| 00
--------------------------------------------------------------------------------

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

   1 - filter("ID"=1)
   2 - access("NAME" LIKE 'y%')
       filter("NAME" LIKE 'y%')

SQL> select * from atest where id=1 and name like '%y';

未选定行

--------------------------------------------------------------------------------
| Id | Operation                   | Name       | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |            |     1 |    65 |     2   (0)|
|* 1 | TABLE ACCESS BY INDEX ROWID| ATEST      |     1 |    65 |     2   (0)|
|* 2 |   INDEX RANGE SCAN          | IND_IDNAME |     1 |       |     1   (0)|
--------------------------------------------------------------------------------

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

   1 - filter("NAME" LIKE '%y')
   2 - access("ID"=1)

就算是联合索引，不使用reverse的话意义也不大