Oracle like、不等于、隐式转换走索引与不走索引情况
发表于：2024-12-12 作者：千家信息网编辑
千家信息网最后更新 2024年12月12日，1. 概述# like（1）当使用like查询时，后模糊匹配，则走索引，如like 'test%'（2）当使用like查询时，前模糊匹配，则不走索引，如like '%test'# <> 不走索引因为不
千家信息网最后更新 2024年12月12日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安全错误
数据库的锁怎样保障安全
旅馆业网络安全内部管理制度
黄浦区信息软件开发定制平均价格
操作数据库数据积压解决办法
网络安全事故导致的危害英语
数据库更新表数据代码
安徽5g服务器标准机柜虚拟主机
方舟生存计划怎么玩多人服务器
广州睿思网络技术
未来网络技术展望
软件开发项目成员配置
数据库代码期末复习
泸州有为互联网科技有限公司
常熟市禧膳网络技术有限公司
网络安全管理员笔试
数据库怎么看视图
汕头天气预报软件开发
linux图片服务器搭建
云服务器做淘宝安全吗
删除数据库为什么删不掉
方舟赵浩服务器叫什么
网络安全技术批发
曙光服务器属于哪个品牌
软件开发项目公司电话
济南软件开发推荐
软件开发组长职位要求
辽宁语音网络技术基础
ansys怎样到处数据库
软件开发和测试那个好
山西数据库安全箱行业
oneview数据库
