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);



  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
 

用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>


  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
  
44
  
45
  
46
  
47
  
48
  
49
  
50
  
51
  
52
  
53
  
54
  
55
 

上面列举了，能走索引的例子，然后改一下用%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>

  

 

  
1
  
2
  
3
  
4
  
5
  
6
  
7
  
8
  
9
  
10
  
11
  
12
  
13
  
14
  
15
  
16
  
17
  
18
  
19
  
20
  
21
  
22
  
23
  
24
  
25
  
26
  
27
  
28
  
29
  
30
  
31
  
32
  
33
  
34
  
35
  
36
  
37
  
38
  
39
  
40
  
41
  
42
  
43
  
44
  
45
  
46
  
47
  
48
  
49
 

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

文章来源: smilenicky.blog.csdn.net，作者：smileNicky，版权归原作者所有，如需转载，请联系作者。

原文链接：smilenicky.blog.csdn.net/article/details/91149415