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

源：http://blog.csdn.net/haiross/article/details/12974581
评：数据库索引分为 正向与反向索引，模糊查询时 注意编写sql是可以走 索引的

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

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 atest reverse(name);
索引已创建。
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>  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的话意义也不大