oracle右模糊查询不使用索引,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%去匹配也是不走索引的

原文出处:https://www.cnblogs.com/mzq123/p/10989254.html

  • 0
    点赞
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值