ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 反向索引与模糊查询

反向索引与模糊查询

原创 Linux操作系统 作者:安佰胜 时间:2011-05-06 11:23:59 0 删除 编辑
反向索引与模糊查询
---------------------
 
版本
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的话意义也不大
 

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13177610/viewspace-694601/,如需转载,请注明出处,否则将追究法律责任。

下一篇: Reverse Key Indexes
请登录后发表评论 登录
全部评论

注册时间:2009-08-26

  • 博文量
    215
  • 访问量
    625874