位图索引相对于常规的B-tree 索引，有着体积更加小的优势，节省空间。对于重复率特别高的字段，比如性别，比如省份。查询效率要优于B-tree 索引。那为什么我们总被告知在业务库中不要使用呢？
业务库一般要频繁的进行insert,update,delete 操作。下面通过实验测试位图索引对于DML操作的影响。
测试步骤
1.Insert insert update delete 2.delete insert update delete 3.update insert update delete
测试环境构建
create table t_bitmap(id number,sex varchar2(2)); create bitmap index idx_bitmap on t_bitmap(sex); insert into t_bitmap values(1,'M'); insert into t_bitmap values(2,'F'); commit;
SQL> select * from t_bitmap;
ID SEX
---------- ----
1 M
2 F
1. INSERT 操作对其他DML的影响
Session 1 执行： SQL> insert into t_bitmap values (3,'NA'); 1 row created.
Session２执行：
SQL> update t_bitmap set sex='F' where id=1;
1 row updated.
SQL> commit;
Commit complete.
SQL> delete from t_bitmap where id=1;
1 row deleted.
SQL> insert into t_bitmap values(1,'M');
1 row created.
SQL> commit;
Commit complete.
SQL> select * from t_bitmap;
ID SEX
---------- ----
1 M
2 F
Session 1 执行： SQL> commit; Commit complete. SQL> select * from t_bitmap; ID SEX ---------- ---- 1 M 2 F 3 NA
可见在有位图索引的列上做insert 操作不会发生任何阻塞。
2. DELETE 操作对其他操作的影响
Session 1 执行： SQL> delete from t_bitmap where id=1; 1 row deleted.
Session2 执行： SQL> update t_bitmap set sex='M' where id=2; --发生阻塞 update t_bitmap set sex='M' where id=2 * ERROR at line 1: ORA-01013: user requested cancel of current operation SQL> SQL> SQL> delete from t_bitmap where id=2; 1 row deleted. SQL> delete from t_bitmap where id=3; 1 row deleted. SQL> insert into t_bitmap values (3,'F'); 1 row created. SQL> commit; Commit complete.
Session1 执行： SQL> commit; Commit complete. SQL> select * from t_bitmap; ID SEX ---------- ---- 3 F
可见delete 操作会对update 操作造成阻塞，但不影响insert ，delete 操作。
3. Update 操作对其他DML操作的影响
Session1 执行： truncate table t_bitmap; insert into t_bitmap values(1,'M'); insert into t_bitmap values(2,'F'); commit; SQL> select * from t_bitmap; ID SEX ---------- ---- 1 M 2 F SQL> update t_bitmap set sex='F' where id=1; 1 row updated.
Session2 执行： SQL> UPDATE T_BITMAP SET SEX='M' WHERE ID=2; --update 被阻塞 UPDATE T_BITMAP SET SEX='M' WHERE ID=2 * ERROR at line 1: ORA-01013: user requested cancel of current operation SQL> DELETE FROM T_BITMAP WHERE ID=2; --delete 被阻塞 DELETE FROM T_BITMAP WHERE ID=2 * ERROR at line 1: ORA-01013: user requested cancel of current operation SQL> INSERT INTO T_BITMAP VALUES (3,'F'); --insert 被阻塞
Session1 执行： SQL> select * from dba_locks where (lock_id1,lock_id2) in (SELECT lock_id1,lock_id2 FROM DBA_LOCKS WHERE BLOCKING_OTHERS='Blocking'); --session2 执行insert时查询到的锁情况。 SESSION_ID LOCK_TYPE MODE_HELD MODE_REQUE LOCK_ID1 LOCK_ID2 LAST_CONVERT BLOCKING_OTHERS ---------- -------------------- -------------------- ---------- ---------- ---------- ------------ -------------------- 45 Transaction Exclusive None 131088 1478 563 Blocking 49 Transaction None Share 131088 1478 445 Not Blocking
SQL> select sid from v$mystat where rownum=1;
SID
----------
45
SQL> commit;
Commit complete.
SQL> select * from dba_locks where (lock_id1,lock_id2) =(SELECT lock_id1,lock_id2 FROM DBA_LOCKS WHERE BLOCKING_OTHERS='Blocking');
no rows selected
--可见，对位图索引列的更新操作，会阻塞 insert,delete,update 操作
总结
通过上面的实验，我们得出结论。业务库不是不能使用位图索引，但是有限制：对位图索引列不能存在update 操作，否则将阻塞该表几乎所有的DML操作, 引起严重的性能问题。如果只是做select,insert,delete 操作，是不会互相影响的。