oracle索引对模糊查询,oracle细节之like模糊查询不能走索引？

最新推荐文章于 2024-01-17 17:28:30 发布

weixin_39890814

最新推荐文章于 2024-01-17 17:28:30 发布

阅读量321

点赞数

文章标签： oracle索引对模糊查询

本文纠正了关于SQL模糊匹配不能走索引的普遍误解，通过实例展示了当LIKE操作符以特定模式（如'LJB%'）进行搜索时，可以利用索引进行范围扫描。然而，当模式以通配符 '%' 开头（如'%LJB'），则通常无法利用索引，导致全表扫描。了解这些细节对于SQL性能优化至关重要。

摘要由CSDN通过智能技术生成

这里要纠正一个网上很多教程说的模糊匹配不能走索引的说法，因为在看《收获，不止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

用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

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

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

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

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

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

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

| 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