反向索引与模糊查询
反向索引应用于前模糊的用法简介:
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> 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
----------------------------------------------------------------
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> conn an/an
已连接。
SQL> create table atest(id int,name varchar2(100));
表已创建。
表已创建。
SQL> insert into atest select 1,'anbaisheng' from dual;
已创建 1 行。
已创建 1 行。
SQL> insert into atest select 2,'xiangxiang' from dual;
已创建 1 行。
已创建 1 行。
SQL> insert into atest select 3,'baby' from dual;
已创建 1 行。
已创建 1 行。
SQL> commit;
提交完成。
提交完成。
SQL> select * from atest;
ID NAME
---------- --------------------
1 anbaisheng
2 xiangxiang
3 baby
ID NAME
---------- --------------------
1 anbaisheng
2 xiangxiang
3 baby
创建正续索引
SQL> create index ind_name on atest(name);
SQL> create index ind_name on atest(name);
索引已创建。
SQL> set autotrace on
SQL> select * from atest where name like 'a%';
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%')
---------- --------------------
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
---------- --------------------
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> 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
---------- --------------------
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关键字前模糊也没用,后模糊无效
--------------------------------------------------------------------------------
| 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));
这个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的话意义也不大
| 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的话意义也不大