Oracle like、不等于、隐式转换走索引与不走索引情况
发表于：2025-02-01 作者：千家信息网编辑
千家信息网最后更新 2025年02月01日，1. 概述# like（1）当使用like查询时，后模糊匹配，则走索引，如like 'test%'（2）当使用like查询时，前模糊匹配，则不走索引，如like '%test'# <> 不走索引因为不
千家信息网最后更新 2025年02月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安全错误
数据库的锁怎样保障安全
根据来源不同 风险数据库
葡萄基因组数据库
云尔达软件开发
深圳市光通网络技术
计算机网络安全主要措施
洪江市软件开发学校
乌鲁木齐软件开发费用咨询
昆明网络安全工程师培训机构
信息网络安全项目
菜鸟网络技术创新
2k22服务器垃圾
软件开发商员工待遇
山东联想服务器批发
科密a1数据库安装
移动互联网软件开发比赛
恒智天成软件开发商是哪
江西app软件开发大概多少钱
软件开发法令
数据库文件太大无法上传
内蒙古网络技术专科学校
中信证券软件开发待遇如何
东风乡软件开发培训学校
三门峡橘子网络技术有限公司
工控网络安全销售专业术语
生活中威胁数据库的例子
电信卡手机网络服务器代码多少
眉山软件开发定做
网络安全发言稿部队
非关联性数据库技术
服务器与工控机
