oracle like模糊查询不能走索引详解数据库

这里要纠正一个网上很多教程说的模糊匹配不能走索引的说法，因为在看《收获，不止SQL优化》一书，里面举例说到了，并且自己也跟着例子实践了一下，确实like一些特殊情况也是可以走索引的

例子来自《收获，不止SQL优化》一书，实践准备：

//建表，注意要非空数据 
drop table t purge; 
create table t as select * from dba_objects where object_id is not null; 
 
select * from t; 
 
//更新数据并建索引，用来测试 
update t set object_id=rownum; 
 
update t set object_name='AAALJB' where object_id=8; 
 
update t set object_name='LJBAAA' where object_id=10; 
 
create index idx_object_name on t(object_name); 
 

用set autotrace on用来打印执行计划，这里注意了，用LJB%去模糊匹配，然后观察执行计划，发现是索引范围扫描INDEX RANGE SCAN 的，因为去匹配LJB开头的数据，索引是可以范围查询并匹配到，所以是能走范围索引扫描的，所以网上的说法是不全面的

SQL> set autotrace on 
SQL> select object_id,object_name from t where object_name like 'LJB%'; 
 
 OBJECT_ID 
---------- 
OBJECT_NAME 
-------------------------------------------------------------------------------- 
 
        10 
LJBAAA 
 
 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 1138138579 
 
-------------------------------------------------------------------------------- 
 
--------------- 
 
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%C 
 
PU)| Time     | 
 
-------------------------------------------------------------------------------- 
 
--------------- 
 
|   0 | SELECT STATEMENT            |                 |     1 |    79 |     4 
(0)| 00:00:01 | 
 
|   1 |  TABLE ACCESS BY INDEX ROWID| T               |     1 |    79 |     4 
(0)| 00:00:01 | 
 
|*  2 |   INDEX RANGE SCAN          | IDX_OBJECT_NAME |     1 |       |     3 
(0)| 00:00:01 | 
 
-------------------------------------------------------------------------------- 
 
--------------- 
 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   2 - access("OBJECT_NAME" LIKE 'LJB%') 
       filter("OBJECT_NAME" LIKE 'LJB%') 
 
Note 
----- 
   - dynamic sampling used for this statement (level=2) 
 
SQL> 

上面列举了，能走索引的例子，然后改一下用%LJB去匹配，看看能不能走索引？

SQL> set autotrace on 
SQL> select object_id,object_name from t where object_name like '%LJB'; 
 
 OBJECT_ID 
---------- 
OBJECT_NAME 
-------------------------------------------------------------------------------- 
 
         8 
AAALJB 
 
 
 
执行计划 
---------------------------------------------------------- 
Plan hash value: 1601196873 
 
-------------------------------------------------------------------------- 
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
-------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT  |      |    12 |   948 |   288   (1)| 00:00:04 | 
|*  1 |  TABLE ACCESS FULL| T    |    12 |   948 |   288   (1)| 00:00:04 | 
-------------------------------------------------------------------------- 
 
Predicate Information (identified by operation id): 
--------------------------------------------------- 
 
   1 - filter("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%LJB') 
 
Note 
----- 
   - dynamic sampling used for this statement (level=2) 
 
 
统计信息 
---------------------------------------------------------- 
          0  recursive calls 
          0  db block gets 
       1032  consistent gets 
          0  physical reads 
          0  redo size 
        503  bytes sent via SQL*Net to client 
        419  bytes received via SQL*Net from client 
          2  SQL*Net roundtrips to/from client 
          0  sorts (memory) 
          0  sorts (disk) 
          1  rows processed 
 
SQL>

例子可以看出是全表扫描的，不走索引，因为%LJB这种匹配，索引不能确认唯一性，同样的%LJB%去匹配也是不走索引的