用解释计划评估创建索引后对单表查询效率的影响
有这么一张表:
create table hy_emp( empno number(6,0) primary key, deptno number(6,0) not null, ename nvarchar2(20) not null )
这么给它充值:
insert into hy_emp select rownum,dbms_random.value(1,12),dbms_random.string('*',dbms_random.value(6,20)) from dual connect by level<500000 order by dbms_random.random
下面我们对ename字段进行查询并取解释计划查看效率:
EXPLAIN PLAN FOR select * from hy_emp where ename='OODFSK' select * from table(dbms_xplan.display);
得到解释计划如下:
Plan hash value: 910676026 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 30 | 1440 | 754 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| HY_EMP | 30 | 1440 | 754 (1)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ENAME"=U'OODFSK') Note ----- - dynamic statistics used: dynamic sampling (level=2)
Cost是754。然后我们在ename字段上添加索引:
create index hy_emp_ename on hy_emp(ename);
再次查看解释计划:
EXPLAIN PLAN FOR select * from hy_emp where ename='OODFSK' select * from table(dbms_xplan.display);
解释计划如下:
Plan hash value: 2159543048 ---------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 48 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| HY_EMP | 1 | 48 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | HY_EMP_ENAME | 1 | | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ENAME"=U'OODFSK') Note ----- - dynamic statistics used: dynamic sampling (level=2)
Cost一下子从754变到4了,只剩下零头了,看来加索引效果显著。
然后我们删掉这个索引:
drop index hy_emp_ename
再对ename和deptno两个字段进行联合查询并查看执行计划:
EXPLAIN PLAN FOR select * from hy_emp where ename='OODFSK' and deptno=9 select * from table(dbms_xplan.display);
解释计划如下:
Plan hash value: 910676026 ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 55 | 2640 | 754 (1)| 00:00:01 | |* 1 | TABLE ACCESS FULL| HY_EMP | 55 | 2640 | 754 (1)| 00:00:01 | ---------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("ENAME"=U'OODFSK' AND "DEPTNO"=9) Note ----- - dynamic statistics used: dynamic sampling (level=2)
Cost是754.
再给ename和deptno字段添加索引:
create index hy_emp_two on hy_emp(ename,deptno);
然后再看解释计划:
EXPLAIN PLAN FOR select * from hy_emp where ename='OODFSK' and deptno=9 select * from table(dbms_xplan.display);
解释计划如下:
Plan hash value: 3007671827 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 48 | 4 (0)| 00:00:01 | | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| HY_EMP | 1 | 48 | 4 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | HY_EMP_TWO | 1 | | 3 (0)| 00:00:01 | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ENAME"=U'OODFSK' AND "DEPTNO"=9) Note ----- - dynamic statistics used: dynamic sampling (level=2)
又从754变成4了。
结论:索引对于提升查询效率明显,但前提是要和查询条件对应上,查几个字段就为这几个字段建立索引最好。
--2020-03-17--
分类:
Oracle.索引
, Oracle.解释/执行计划
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· Linux系列:如何用heaptrack跟踪.NET程序的非托管内存泄露
· 开发者必知的日志记录最佳实践
· SQL Server 2025 AI相关能力初探
· Linux系列:如何用 C#调用 C方法造成内存泄露
· AI与.NET技术实操系列(二):开始使用ML.NET
· 无需6万激活码!GitHub神秘组织3小时极速复刻Manus,手把手教你使用OpenManus搭建本
· C#/.NET/.NET Core优秀项目和框架2025年2月简报
· Manus爆火,是硬核还是营销?
· 终于写完轮子一部分:tcp代理 了,记录一下
· 【杭电多校比赛记录】2025“钉耙编程”中国大学生算法设计春季联赛(1)
2019-03-17 【Canvas与游戏】空战游戏1.18