Oracle like、不等于、隐式转换走索引与不走索引情况
发表于：2025-11-07 作者：千家信息网编辑
千家信息网最后更新 2025年11月07日，1. 概述# like（1）当使用like查询时，后模糊匹配，则走索引，如like 'test%'（2）当使用like查询时，前模糊匹配，则不走索引，如like '%test'# <> 不走索引因为不
千家信息网最后更新 2025年11月07日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安全错误
数据库的锁怎样保障安全
科技互联网商标属于多少类
服务器能当电脑主机吗
kafka服务器配置
软件开发需要用什么程序
适合学生做的网络安全实验
关于网络安全的手抄报词语
以服务器数据库连接失败
怎么辨别独立服务器跟云服务器
网络安全警察在大学要学什么
南京软件开发工作好找吗
风力发电机软件开发
古墓丽影是用哪款软件开发的
大专学网络技术有前途吗
天谕官服服务器互通和
ios后台服务器搭建
网络安全课程设计ppt
腾讯硕士软件开发工资待遇
学生网络技术开发价值
监控云服务器不能登陆
构建内嵌式数据库的应用
图解网络安全知识科普
舟山网络安全宣传周启动
韶关app软件开发外包
软件开发加班情况
中国能源研究会网络安全
绵阳网络技术服务
深圳市蓝谷网络技术有限公司
数据库收缩
软件开发展览会
计算机网络技术 怎么学
