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 

-------------------------------------------------------------------------------- 

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) 

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

SQL set autotrace on 

SQL select object_id,object_name from t where object_name like %LJB; 

 OBJECT_ID 

---------- 

OBJECT_NAME 

-------------------------------------------------------------------------------- 

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) 


SQL 

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

原创文章，作者：ItWorker，如若转载，请注明出处：https://blog.ytso.com/5063.html