位图索引对单表查询效率的影响（使用解释计划评估）

有这么一张表：

create table hy_emp(
id number(7,0) not null primary key,
deptid number(2,0) not null,
name nvarchar2(20) not null)

这样给它充值：

insert into hy_emp
select rownum,dbms_random.value(1,10),dbms_random.string('*',dbms_random.value(3,18))
from dual
connect by level<=400000
order by dbms_random.random

然后查看以下SQL的解释计划：

EXPLAIN PLAN FOR
select * from hy_emp where deptid=10 and name='ZUVG'

select * from table(dbms_xplan.display);

解释计划如下：

Plan hash value: 910676026
 
----------------------------------------------------------------------------
| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |        |    34 |  1632 |   548   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| HY_EMP |    34 |  1632 |   548   (1)| 00:00:01 |
----------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - filter("DEPTID"=10 AND "NAME"=U'ZUVG')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

没有任何索引，自然是全表扫描，Cost是548.

然后我们给deptid和name两个字段加个位图索引：

create bitmap index hy_emp_idx_2 on hy_emp(deptid,name);

再次查看解释计划：

Plan hash value: 1386009635
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |     1 |    48 |    10   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| HY_EMP       |     1 |    48 |    10   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |              |       |       |            |          |
|*  3 |    BITMAP INDEX SINGLE VALUE        | HY_EMP_IDX_2 |       |       |            |          |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPTID"=10 AND "NAME"=U'ZUVG')
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

Cost降到了10，因为新加的索引已经用上了。

 

然后我们来验证全模糊查询是否能用上这个索引：

EXPLAIN PLAN FOR
select * from hy_emp where deptid=10 and name like '%ZUVG%'
select * from table(dbms_xplan.display);

解释计划如下：

Plan hash value: 2088399487
 
----------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |    22 |  1056 |   501   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| HY_EMP       |    22 |  1056 |   501   (0)| 00:00:01 |
|   2 |   BITMAP CONVERSION TO ROWIDS       |              |       |       |            |          |
|*  3 |    BITMAP INDEX RANGE SCAN          | HY_EMP_IDX_2 |       |       |            |          |
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPTID"=10)
       filter("NAME" LIKE U'%ZUVG%' AND "DEPTID"=10)
 
Note
-----
   - dynamic statistics used: dynamic sampling (level=2)

从上面可以发现，新加的索引还是用上了，Cost也比全表查询要低一点，虽然低的不多。看来网上说的模糊查询会导致索引全失效的说法有点偏颇了，至少位图索引能起到聊胜于无的作用。

--2020-03-17--