Oracle like、不等于、隐式转换走索引与不走索引情况
发表于：2024-09-22 作者：千家信息网编辑
千家信息网最后更新 2024年09月22日，1. 概述# like（1）当使用like查询时，后模糊匹配，则走索引，如like 'test%'（2）当使用like查询时，前模糊匹配，则不走索引，如like '%test'# <> 不走索引因为不
千家信息网最后更新 2024年09月22日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安全错误
数据库的锁怎样保障安全
互联网 科技案例
扬州软件开发有哪些
网络安全青少年日活动方案
石家庄便捷式设备管理软件开发
桂阳学it软件开发培训学校
计算机网络技术专科科目
数据库如何使用快捷键
网络安全股票有没有上长机会
滨湖区软件开发维修电话
网络安全风险评估技术
服务器安装盘
在线iphone软件开发
打印机服务器控制打印权限
网络安全工程与法学双学士
数据库为何无法编辑关系
美国一号网络技术有限公司
无纸化会议管理服务器厂家
win服务器面板
河北智慧人口管理系统软件开发
落实网络安全责任制会议内容
数据库技术及应用习题答案
数据库查询常见问题和解决方案
网络安全 互联网开放端口
服务器补丁更新 管理器
远景能源软件开发中心
人大代表宣传网络安全
联想rd330服务器
创建数据库的通用软件
查看数据库中是否存在
技校计算机网络技术专业就业
