反向索引与模糊查询
反向索引应用于前模糊的用法简介：
1、反向索引
SQL> create index ind_name on atestreverse(name);
SQL> select * from atest where name likereverse('%y');
2、联合部分反向索引
SQL> create index ind_idname on atest (id ,reverse(name)); 索引已创建。 SQL> select * from atest where id=1 and name likereverse('%y'); 未选定行
具体用法展示如下：
反向索引与模糊查询
---------------------版本SQL> select * from v$version; BANNER
----------------------------------------------------------------
Oracle Database10gEnterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE10.2.0.4.0Production
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> createindexind_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
---------------------------------------------------------------------------
| 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 likereverse('%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 likereverse('%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的话意义也不大
