Oracle 使用位图索引 Cost降低200倍! 探讨位图索引的利与弊

一.简介

位图索引（Bitmap Index） 是 Oracle 数据库中一种特殊类型的索引，适用于低基数（Low Cardinality）列，即那些列中可选值相对较少的情况下使用。它与常规的 B-tree 索引不同，位图索引通过位图（bitmap）来表示列的不同取值及其在表中的位置，从而有效加速特定类型的查询。但位图索引有很大的弊端，使用不当会造成大量的等待事件，甚至造成系统崩溃，位图索引需要谨慎使用。

二.实验 

create table bm_table as select * from dba_objects;
insert into bm_table select * from bm_table;
/
/
/
/
/
update bm_table set object_id = rownum;

此时表内记录约230万。

未建立索引的执行计划：

set autotrace traceonly
set linesize 200
select count(*) from bm_table;

建立普通索引的执行计划

create index idx1 on bm_table(object_id);

此时仍走全表扫描，原因是索引无法存储空行，CBO不会为整行都为空行的表走索引。

具体的实验 之前也做过笔记：Oracle SQL语句没有过滤条件，究竟是否会走索引？？_oracle sql 是否走索引-CSDN博客

 设置object_id列为not null，让语句走索引。

alter table bm_table modify object_id not null;
select count(*) from bm_table;

意料之中，走索引快速全扫描，一次性读取多个数据块，速度要快于INDEX FULL SCAN。

建立位图索引的执行计划

create bitmap index index2 on bm_table(status);

根据位图索引的特性，这个索引我们建在status列上，因为status列 可选值相对较少。

select count(*) from bm_table;

执行结果比较

为什么位图索引这么快？

这是由位图索引的原理决定的

如果某个列有三个可能的值（例如，性别列有 "M"、"F"、"U"），那么 Oracle 会为每个可能的值创建一个位图，如下所示：

值为 'M' 的位图: 101001
值为 'F' 的位图: 010100
值为 'U' 的位图: 000010
 

位图索引以二进制的形式存储，性能极高。

三.位图索引的弊端

insert into bm_table(OBJECT_ID,STATUS) values(195555,'invalid');
select distinct status from bm_table;

此时模拟DML语句。

当前会话id：

select sid from v$mystat where rownum=1;

另开一个会话2，此时的会话id：

会话1执行DML操作：

insert into bm_table(OBJECT_ID,STATUS) values(195555,'invalid');

会话2执行DML操作：

insert into bm_table(OBJECT_ID,STATUS) values(195555,'invalid')；

发现sql 被锁，一直在等待。

中止sql执行，换一条sql执行。

insert into bm_table(OBJECT_ID,STATUS) values(195555,'valid')；

sql 反而又执行成功。

如果不更新status列呢?

insert into bm_table(OBJECT_ID) values(195555);

也会更新成功。 

四.总结

在会话一中，我们做了插入status列 为invalid， 在会话二中，我们做了插入status 列为invalid 造成sql等待，又做了插入status 列 为 valid 反而插入成功，继续做插入不涉及status列 也可以更新成功。

结合位图索引结构与存储方式，发现

如果在表的某列建了位图索引，那么在做DML操作时（包括delete与update），会造成 位图索引列=‘x’，的列全被锁住，例如，在 gender 列上有位图索引，此时做DML 操作更新 某行gender = 男，那么此时 所有会话 想更新 gender = 男 全都会被锁住，直到 初始更新 gengder=男 的会话 事务提交。

就OLTP系统来说，这样是不能容忍的，所以 ，位图索引仅仅适用于很少更新的场场景，并且列的取值大多重复的场景，做实验得出，如果列的取值重复度不高，位图索引的效率甚至要比全表扫描还要差！ 

所以 如果只知道位图索引只适用于列的重复值较多，不知道位图索引会造成所有会话的sql等待而盲目创建位图索引，那后果是很严重的。