Oracle like、不等于、隐式转换走索引与不走索引情况
发表于：2026-11-01 作者：千家信息网编辑
千家信息网最后更新 2026年11月01日，1. 概述# like（1）当使用like查询时，后模糊匹配，则走索引，如like 'test%'（2）当使用like查询时，前模糊匹配，则不走索引，如like '%test'# <> 不走索引因为不
千家信息网最后更新 2026年11月01日Oracle like、不等于、隐式转换走索引与不走索引情况1. 概述
2.测试
1. 概述
# like（1）当使用like查询时，后模糊匹配，则走索引，如like 'test%'（2）当使用like查询时，前模糊匹配，则不走索引，如like '%test'# <> 不走索引因为不等于，即等于大量数据，所以不走索引# 隐式转换，当发生在索引列时，不走索引，发生在条件值列时，走索引（1）如果隐式转换发生在值列，则走索引，例如查询使用日期查询时，select * from test_implic where bir_date = '20180122 14:22:32';（2）如果索引列发生了隐式转换，则不走索引，如列数据类型为varchar2，使用如下查询时select bir_date from test_implic where id = 2000;（3）当number列等于字符串时，走索引
2.测试
（1） like 后模糊匹配走索引 like 前模糊匹配走全表
# 创建测试表create table test_bind(id number,name varchar2(20));#插入数据declarei number;beginfor i in 1..100000loopinsert into test_bind values(i,'haha');end loop;end;/declarei number;beginfor i in 100000..100010loopinsert into test_bind values(i,'test');end loop;end;/# 创建索引create index IDX_TEST_BIND on test_bind(name);# 收集统计信息exec dbms_stats.gather_table_stats('LIBAI','TEST_BIND');# 查询，后模糊匹配，可以看到走了索引LIBAI@honor1 > set autotrace onLIBAI@honor1 > select * from test_bind where name like 'te%'; ID NAME---------------------------------------- ---------------------------------------- 100001 test 100002 test 100003 test 100004 test 100005 test 100006 test 100007 test 100008 test 100009 test 100010 test10 rows selected.Elapsed: 00:00:00.00Execution Plan----------------------------------------------------------Plan hash value: 2889536435---------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 9 | 90 | 3 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TEST_BIND | 9 | 90 | 3 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TEST_BIND | 9 | | 2 (0)| 00:00:01 |---------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("NAME" LIKE 'te%') filter("NAME" LIKE 'te%')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 5 consistent gets 0 physical reads 0 redo size 782 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed # 前模糊匹配，可以看到走了全表扫描LIBAI@honor1 > select * from test_bind where name like '%st'; ID NAME---------------------------------------- ---------------------------------------- 100001 test 100002 test 100003 test 100004 test 100005 test 100006 test 100007 test 100008 test 100009 test 100010 test10 rows selected.Elapsed: 00:00:00.02Execution Plan----------------------------------------------------------Plan hash value: 3519963602-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 5001 | 50010 | 69 (2)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_BIND | 5001 | 50010 | 69 (2)| 00:00:01 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("NAME" LIKE '%st' AND "NAME" IS NOT NULL)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 236 consistent gets 0 physical reads 0 redo size 734 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed（2） <> 不走索引
LIBAI@honor1 > select * from test_bind where name <> 'test'; ID NAME---------------------------------------- ---------------------------------------- 100001 test 100002 test 100003 test 100004 test 100005 test 100006 test 100007 test 100008 test 100009 test 100010 test10 rows selected.Elapsed: 00:00:00.01Execution Plan----------------------------------------------------------Plan hash value: 3519963602-------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |-------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 18 | 180 | 69 (2)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_BIND | 18 | 180 | 69 (2)| 00:00:01 |-------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter("NAME"<>'haha')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 236 consistent gets 0 physical reads 0 redo size 734 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 10 rows processed（3） 隐式转换
# 构造测试环境
create table test_implic (id varchar2(20),name varchar2(20),bir_date date default sysdate);declarei varchar2(10);beginfor i in 1..10000loopinsert into test_implic values(i,'czh',sysdate);end loop;commit;end;/create index idx_test_implic_id on test_implic(id);create index idx_test_implic_bir_date on test_implic(bir_date);exec dbms_stats.gather_table_stats('LIBAI','TEST_IMPLIC');# 当varchar2类型等于数字时，不走索引
LIBAI@honor1 > select bir_date from test_implic where id = 2000;BIR_DATE-------------------2020-01-19 20:00:51Execution Plan----------------------------------------------------------Plan hash value: 965190314---------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |---------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 11 (0)| 00:00:01 ||* 1 | TABLE ACCESS FULL| TEST_IMPLIC | 1 | 13 | 11 (0)| 00:00:01 |---------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(TO_NUMBER("ID")=2000)Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 38 consistent gets 0 physical reads 0 redo size 531 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processedLIBAI@honor1 > select bir_date from test_implic where id = to_char(2000);BIR_DATE-------------------2020-01-19 20:00:51Execution Plan----------------------------------------------------------Plan hash value: 3908402167--------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 13 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TEST_IMPLIC | 1 | 13 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TEST_IMPLIC_ID | 1 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"='2000')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 4 consistent gets 4 physical reads 0 redo size 531 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 1 rows processed# 当number等于字符串时，走索引
LIBAI@honor1 > select * from test_bind where id = '1000'; ID NAME---------------------------------------- ---------------------------------------- 1000 hahaExecution Plan----------------------------------------------------------Plan hash value: 2345277976------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 10 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TEST_BIND | 1 | 10 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TEST_BIND_ID | 1 | | 1 (0)| 00:00:01 |------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("ID"=1000)Statistics---------------------------------------------------------- 14 recursive calls 0 db block gets 33 consistent gets 0 physical reads 0 redo size 595 bytes sent via SQL*Net to client 519 bytes received via SQL*Net from client 2 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1 rows processed# 当日期等于字符串时，走索引
LIBAI@honor1 > select * from test_implic where bir_date = '20180122 14:22:32';no rows selectedExecution Plan----------------------------------------------------------Plan hash value: 3390782276--------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |--------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | 17 | 2 (0)| 00:00:01 || 1 | TABLE ACCESS BY INDEX ROWID| TEST_IMPLIC | 1 | 17 | 2 (0)| 00:00:01 ||* 2 | INDEX RANGE SCAN | IDX_TEST_IMPLIC_BIR_DATE | 1 | | 1 (0)| 00:00:01 |--------------------------------------------------------------------------------------------------------Predicate Information (identified by operation id):--------------------------------------------------- 2 - access("BIR_DATE"='20180122 14:22:32')Statistics---------------------------------------------------------- 1 recursive calls 0 db block gets 2 consistent gets 4 physical reads 0 redo size 466 bytes sent via SQL*Net to client 508 bytes received via SQL*Net from client 1 SQL*Net roundtrips to/from client 0 sorts (memory) 0 sorts (disk) 0 rows processed
索引
查询
字符
字符串
数据
测试
日期
类型
信息
数字
条件
环境
统计
情况
数据库的安全要保护哪些东西
数据库安全各自的含义是什么
生产安全数据库录入
数据库的安全性及管理
数据库安全策略包含哪些
海淀数据库安全审计系统
建立农村房屋安全信息数据库
易用的数据库客户端支持安全管理
连接数据库失败ssl安全错误
数据库的锁怎样保障安全
湖北九亚互联网络科技有限公司
互联网科技公司起名
云之行互联网科技
中国互联网科技工作委员会毛军
互联网科技公司发展
伴你行互联网科技有限公司
长亮科技互联网金融
互联网金融科技委员会
互联网科技有限公司 翻译
智腾互联网科技有限公司
崇明区互联网教育科技售后服务
移动互联网年度科技创新
深圳育鹏互联网科技有限公司招聘
中国互联网十大科技股排名
晋城互联网科技
上海京穆互联网科技有限公司
上海互联网科技公司ppt
恒生科技指数 中概互联网
中国主要互联网科技公司
颐高云石互联网科技
咖啡
上海挚品互联网科技有限公司
互联网下的科技发展
互联网金融与金融科技
中国 科技 互联网
苏州互联网科技贷款公司
佰商互联网科技有限公司
字节跳动和互联网科技
珠海东科互联网科技有限公司
福仕德教育科技互联网
