位图索引 Bitmap index

1) 原理

一个键值对应很多行（rowid）， 格式：键值start_rowid end_rowid 位图

2) 适用场合
列的基数很少，可枚举，重复值很多，数据不会被经常更新
3) 优点
OLAP 例如报表类数据库重复率高的数据特定类型的查询例如count、or、and 等逻辑操
作因为只需要进行位运算即可得到我们需要的结果
4) 缺点
不适合重复率低的字段，还有经常DML 操作（insert，update，delete），因为位图索引的
锁代价极高，修改一个位图索引段影响整个位图段，例如修改一个键值，会影响同键值的多行，所
以对于OLTP 系统位图索引基本上是不适用的

5) 实验
位图索引和B-tree 索引的性能比较
set pagesize 100; 设置页大小
利用dba_objects 数据字典创建一个20 万行的表
SQL> create table leo_bm_t1 as select * from dba_objects;
Table created.
SQL> insert into leo_bm_t1 select * from leo_bm_t1;
50364 rows created.
SQL> set autotrace off;
SQL> insert into leo_bm_t1 select * from leo_bm_t1;
100728 rows created.
SQL> select count(*) from leo_bm_t1;
COUNT(*)
----------
201456
因object_type 字段重复值较高，故在此字段上创建bitmap 索引
SQL > create bitmap index leo_bm_t1_index on leo_bm_t1(object_type);
Index created.
创建一个和leo_bm_t1 表结构一模一样的表leo_bm_t2，并在object_type 列上创建一个B-tree 索引（20
万行记录）
SQL > create table leo_bm_t2 as select * from leo_bm_t1;
Table created.
SQL > create index leo_bm_t2_bt_index on leo_bm_t2(object_type);
Index created.

对比位图索引和B-tree 索引所占空间大小,很明显位图要远远小于B-tree 索引所占用的空
间，节约空间特性也是我们选择位图的理由之一
SQL> col segment_name for a20
SQL > select segment_name,bytes from user_segments where segment_type='INDEX' and
segment_name in ('LEO_BM_T1_INDEX','LEO_BM_T2_BT_INDEX');
SEGMENT_NAME BYTES
-------------------- ----------
LEO_BM_T1_INDEX 131072
LEO_BM_T2_BT_INDEX 5242880
显示执行计划和统计信息
set autotrace trace exp stat;
在创建有位图索引的表上做count 操作对比执行计划
SQL> select count(*) from leo_bm_t1 where object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 3251686305

-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 1 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
| 2 | BITMAP CONVERSION COUNT | | 9321 | 100K| 1 (0)| 00:00:01 |
|* 3 | BITMAP INDEX SINGLE VALUE| LEO_BM_T1_INDEX | | | | |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
7 recursive calls
0 db block gets
63 consistent gets
0 physical reads
0 redo size

412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
SQL > select count(*) from leo_bm_t2 where object_type='TABLE';
Execution Plan
----------------------------------------------------------
Plan hash value: 613433245

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 13 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 11 | | |
|* 2 | INDEX RANGE SCAN| LEO_BM_T2_BT_INDEX | 4563 | 50193 | 13 (0)| 00:00:01 |
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("OBJECT_TYPE"='TABLE')
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
32 recursive calls
0 db block gets
109 consistent gets
17 physical reads

0 redo size
412 bytes sent via SQL*Net to client
385 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed