用解释计划评估创建索引后对单表查询效率的影响
有这么一张表：
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--
