BMP位图索引详解

下面假设我们要在 EMP 表的 JOB 列上创建一个位图索引，如下：

 

SQL> create BITMAP index job_idx on emp(job);

 

Index created.

 

Oracle 在索引中存储的内容如表 11.-6 所示。

 

表 11.-6 Oracle 如何存储 JOB-IDX 位图索引

值/行

1

2

3

4

5

6

7

8

9

10

11

12

13

14

ANALYST 

0

0

0

0

0

0

0

1

0

1

0

0

1

0

CLERK 

1

0

0

0

0

0

0

0

0

0

1

1

0

1

MANAGER 

0

0

0

1

0

1

1

0

0

0

0

0

0

0

PRESIDENT 

0

0

0

0

0

0

0

0

1

0

0

0

0

0

SALESMAN 

0

1

1

0

1

0

0

0

0

0

0

0

0

0

表 11.-6显示了第8、10和13行的值为ANALYST，而第4、6和7行的值为MANAGER。在此还显示了所有行都不为null（位图索引可以存储 null条目；如果索引中没有null条目，这说明表中没有null行）。如果我们想统计值为MANAGER的行数，位图索引就能很快地完成这个任务。如 果我们想找出JOB为CLERK或MANAGER的所有行，只需根据索引合并它们的位图，如表11.-7所示。

 

表 11.-7 位 OR 的表示

值/行

1

2

3

4

5

6

7

8

9

10

11

12

13

14

CLERK 

1

0

0

0

0

0

0

0

0

0

1

1

0

1

MANAGER 

0

0

0

1

0

1

1

0

0

0

0

0

0

0

CLERK或MANAGER 

1

0

0

1

0

1

1

0

0

0

1

1

0

1

表11.-7清楚地显示出，第1、4、6、7、11、12还有14行满足我们的要求。Oracle如下为每个键值存储位图，使得每个位置表示底层表中的一个rowid，以后如果确实需要访问行时，可以利用这个rowid进行处理。对于以下查询

select count(*) from emp where job = 'CLERK' or job = 'MANAGER；

用位图索引就能直接得出答案。另一方面，对于以下查询：

select * from emp where job = 'CLERK' or job = 'MANAGER'

则需要访问表。在此 Oracle 会应用一个函数把位图中的第 i 位转换为一个 rowid，从而可用于访问表。

归纳：
当根据键值查询时,可以根据起始Rowid和位图状态,快速定位数据.
当根据键值做and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算,快速得出结果行数据.
当select count(XX) 时,可以直接访问索引就快速得出统计数据.
 位图索引的特点
1.Bitmap索引的存储空间
相对于B*Tree索引,位图索引由于只存储键值的起止Rowid和位图,占用的空间非常少.
bitmap的空间占用主要根以下4个因素相关:
a.表的总记录数
b.索引列的键值多少,列的不同值越少,所需的位图就越少.
c.操作的类型,批量插入比单条插入所面的位图要少得多,8i,9i下是这样的,10G则没有这种区别,详见后面的分析.
d.索引列相同键值的物理分布,8i,9i中,不同块上的数据,相同的键值,会建立不同的位图行(段)来表示，10G相同键值则在相同的位图段上
2.Bitmap索引创建的速度
位图索引创建时不需要排序,并且按位存储,所需的空间也少.
B*Tree索引则在创建时需要排序,定位等操作,速度要慢得多.
3.Bitmap索引允许键值为空
B*Tree索引由于不记录空值,当基于is null的查询时,会使用全表扫描,
而对位图索引列进行is null查询时,则可以使用索引.
4.Bitmap索引对表记录的高效访问
当使用count(XX),可以直接访问索引就快速得出统计数据.
当根据位图索引的列进行and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算,在访问数据之前可事先过滤数据.
5.Bitmap索引对批量DML操作只需进行一次索引
由于通过位图反映数据情况,批量操作时对索引的更新速度比B*Tree索引一行一行的处理快得多.
6.Bitmap索引的锁机制
对于B*Tree索引,insert操作不会锁定其它会话的DML操作.
而位图索引,由于用位图反映数据,不同会话更新相同键值的同一位图段,insert、update、delete相互操作都会发锁定。
对于oracle 8i,9i,单行插入时,由于一个位图行(位图段)只记录8行记录,所以最多锁住相同键值的8行数据的DML操作.
而批量插入时，和10G一样,同一键值只有一个位图行（位图段），所以，相同键值的所有数据的DML操作都会被锁住。
下面，针对8i,9i观察一下锁机制：
 SQL> Declare
Begin
For i In 1..9
Loop
   Insert Into H病人挂号记录(Id,No,号别,执行人) Values(i,'G000001',1,'张1');
End Loop;
Commit;
End;
/
SQL> delete H病人挂号记录 where id=1;
不提交，另开一个会话，
 SQL> delete H病人挂号记录 where id=9;
操作可以进行，没有锁定。
 SQL> delete H病人挂号记录 where id=8;
操作等待,由于和另外一个会话操作的记录的位图索引在同一个位图段上（一个位图段最多8行）,所以被锁住了。

另一种情况是位图索引所引起的死锁，该测试环境为10g
 SQL> create bitmap index bit_t_idx on bitmap_t(id);
Index created.


SQL> select * from bitmap_t;
        ID NAME
---------- ----------
         2 A
         3 A
         4 A
         1 A
         1 B

会话1

SQL> delete from bitmap_t where id=1 and name='A';
1 row deleted.
会话2

SQL>  delete from bitmap_t where id=1 and name='B';

hang住，无法执行

会话1
SQL> delete from bitmap_t where id=1 and name='B';
1 row deleted.

此时会话2上出现

SQL> delete bitmap_t where id=1;
 delete from bitmap_t where id=1 and name='B'
             *
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource

由于10g下所有的数据位于同1位图段，所以在执行DML操作时会锁定整个位图段，从而导致死锁。
三.位图索引的适用场合
1.位图索引是Oracle数据库在7.3版本中加入的,8i,9i企业版和个人版支持,标准版不支持.
2.基于规则的优化器无法使用Bitmap索引
3.适应于有大量重复值的列查询
4.对于8i,9i版本，不适用于单行插入,适用于批量插入的数据,
   因为单行插入时，相同键值，每插入8行就会生成一行索引块中的位图段,即使相同的值.
   而批量插入时，相同键值只生成一个位图段，而在10g则没有这种区别

5.由于并发DML操作锁定的是整个位图段的大量数据行，所以位图索引主要是用于OLAP应用,也可以用于OLTP中主要为读操作的表.
 关于bitmap的两个参数
SQL> show parameter bitmap;
NAME                                  TYPE         VALUE
------------------------------------ ----------- ------------------------------
bitmap_merge_area_size                integer      1048576
create_bitmap_area_size               integer      8388608
其中bitmap_merge_area_size是bitmap索引进行合并操作时使用的内存区域,create_bitmap_area_size是创建时使用的内存区域.
8i,9i中,需要根据bitmap的大小以及常见的使用情况来调整.
9i以上,只需设置pga_aggregate_target的值,Oracle即会自动进和内存的调整.

位图索引存储原理
位图索引存储原理
位图索引对数据表的列的每一个键值分别存储为一个位图,Oracle对于不同的版本,不同的操作方式,数据生成均有差别.
对于8i,9i,
下面分3种方式来讨论数据的插入:
a.一次插入一行,插入多行后,一次提交;swdk和jsdk和jdk到底分别
b.每插入一行,提交一次;
c.批量插入方式,一次提交;
对于第一种方式,观察位图索引的变化情况.
a.假设插入8行相同键值的数据,如果以每行方式插入,然后一次提交,则会生成8个位图
 SQL> Insert Into H病人挂号记录(Id,No,号别,执行人) Values(1,'G000001',1,'张1');
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> /
1 row inserted
SQL> commit;
Commit complete

SQL> alter system dump datafile 1 block 40028;
System altered
row#0[7847] flag: -----, lock: 0
col 0; len 3; (3):   d5 c5 31    --键值'张1'
col 1; len 6; (6):   00 40 9c 54 00 00   --rowid的起始位置
col 2; len 6; (6):   00 40 9c 54 00 07   --rowid的终止位置
col 3; len 2; (2):   c8 ff    --位图编码
row#1[7802] flag: -----, lock: 0
col 0; len 3; (3):   d5 c5 31
col 1; len 6; (6):   00 40 9c 54 00 08
col 2; len 6; (6):   00 40 9c 54 00 0f
col 3; len 2; (2):   c8 03
row#2[7780] flag: -----, lock: 0
col 0; len 3; (3):   d5 c5 32
col 1; len 6; (6):   00 40 9c 54 00 08
col 2; len 6; (6):   00 40 9c 54 00 0f
col 3; len 1; (1):   02
row#3[7758] flag: -----, lock: 0
col 0; len 3; (3):   d5 c5 33
col 1; len 6; (6):   00 40 9c 54 00 08
col 2; len 6; (6):   00 40 9c 54 00 0f
col 3; len 1; (1):   03
row#4[7736] flag: -----, lock: 2
col 0; len 3; (3):   d5 c5 34
col 1; len 6; (6):   00 40 9c 54 00 08
col 2; len 6; (6):   00 40 9c 54 00 0f
col 3; len 1; (1):   04
row#5[7714] flag: -----, lock: 2
col 0; len 3; (3):   d5 c5 35
col 1; len 6; (6):   00 40 9c 54 00 08
col 2; len 6; (6):   00 40 9c 54 00 0f
col 3; len 1; (1):   05
----- end of leaf block dump -----
但是,下次再插入一行相同键值的数据时,会自动合并这8行位图为一行位图,并生成一个新的索引位图行存放刚插入行的索引:
 SQL> Insert Into H病人挂号记录(Id,No,号别,执行人) Values(1,'G000001',1,'张1');
1 row inserted
SQL> commit;
Commit complete
SQL> alter system dump datafile 1 block 40028;
System altered

row#0[7847] flag: -----, lock: 2
col 0; len 3; (3):   d5 c5 31
col 1; len 6; (6):   00 40 9c 54 00 00
col 2; len 6; (6):   00 40 9c 54 00 07
col 3; len 2; (2):   c8 ff
row#1[7825] flag: -----, lock: 2
col 0; len 3; (3):   d5 c5 31
col 1; len 6; (6):   00 40 9c 54 00 08
col 2; len 6; (6):   00 40 9c 54 00 0f
col 3; len 1; (1):   00
----- end of leaf block dump -----
b.数据每行提交方式,与上面的情况相似,但有一点不一样,每提交一行,拷贝原来的位图,生成新的位图,并标记原来的位图为已删除,
标记为已删除的位图,只有索引块需要分配新的位图时,才会清除标记为已删除的位图,重用这些空间.
在8i,9i上实验的结果,与ITPUB的<Oracle 数据库性能优化>一书378页一致.
如果1000条相同键值的数据插入,将生成125个包括8条记录的位图行.
c.第三种方式,批量插入数据,insert into H病人挂号记录(Id,No,号别,执行人) select ***方式,
   同一键值,只生成一次位图,只有一个位图.
 SQL> Insert Into H病人挂号记录(Id,No,号别,执行人)
Select 1,'G000001',1,'张1' From dual
Union All
Select 2,'G000002',1,'张1' From dual
Union All
Select 3,'G000003',1,'张1' From dual
Union All
Select 4,'G000004',1,'张1' From dual
Union All
Select 5,'G000005',1,'张1' From dual
Union All
Select 6,'G000006',1,'张1' From dual
Union All
Select 7,'G000006',1,'张1' From dual
Union All
Select 8,'G000006',1,'张1' From dual
Union All
Select 9,'G000006',1,'张1' From dual;
SQL> commit;
Commit complete
SQL> alter system dump datafile 1 block 40028;
System altered

row#0[8006] flag: -----, lock: 2
col 0; len 3; (3):   d5 c5 31
col 1; len 6; (6):   00 40 9c 54 00 00
col 2; len 6; (6):   00 40 9c 54 00 0f
col 3; len 3; (3):   c9 ff 01
row#1[8030] flag: ---D-, lock: 2
col 0; NULL
col 1; NULL
col 2; NULL
col 3; NULL
----- end of leaf block dump -----
所以,位图索引最好采用批量插入方式,这样,每个键值只生成一个位图.而单行数据插入方式,每个键值将每8行数据生成一个位图.
10G的情况,则简单得多.
上面3种方式,相同键值的插入,位图的生成是一样的,只有一个位图,并且,每次提交时,并不会删除以前的位图,而是直接修改对应键值的位图.
每次插入一行数据,插入9行后提交
 row#0[7763] flag: ------, lock: 2, len=29
col 0; len 3; (3):   d5 c5 31
col 1; len 6; (6):   00 00 00 00 00 00
col 2; len 6; (6):   00 40 ef f2 00 0f
col 3; len 8; (8):   f9 e4 d5 dc bc 01 ff 01
----- end of leaf block dump -----
再批量插入9行数据并提交
 row#0[7733] flag: ------, lock: 2, len=30
col 0; len 3; (3):   d5 c5 31
col 1; len 6; (6):   00 00 00 00 00 00
col 2; len 6; (6):   00 40 ef f2 00 17
col 3; len 9; (9):   fa e4 d5 dc bc 01 ff ff 03
----- end of leaf block dump -----
可以看出,10G对位图索引的存储进行了优化,一个键值在索引块中只有一个位图

位图索引的使用情景
位图索引对于相异基数（distinctcard inality）低的数据最为合适（也就是说，与整个数据集的基数相比，这个数据只有很少几个不同的值）。对此做出量化是不太可能的——换句话说，很难定 义低相异基数到底是多大。在一个有几千条记录的数据集中，2就是一个低相异基数，但是在一个只有两行的表中，2就不能算是低相异基数了。而在一个有上千万 或上亿条记录的表中，甚至100,000都能作为一个低相异基数。所以，多大才算是低相异基数，这要相对于结果集的大小来说。这是指，行集中不同项的个数 除以行数应该是一个很小的数（接近于0）。例如，GENDER列可能取值为M、F和NULL。如果一个表中有20,000条员工记录，那么 3/20000=0.00015。类似地，如果有100,000个不同的值，与11.,000,000条结果相比，比值为0.01，同样这也很小（可算是 低相异基数）。这些列就可以建立位图索引。它们可能不适合建立B*树索引，因为每个值可能会获取表中的大量数据（占很大百分比）。
不过，在某些情况下，位图并不合适。位图索引在读密集的环境中能很好地工作，但是对于写密集的环境则极不适用 。原因在于，一个位图索引键条目指向多行。如果一个会话修改了所索引的数据，那么在大多数情况下，这个索引条目指向的所有行都会被锁定。Oracle无法 锁定一个位图索引条目中的单独一位；而是会锁定这个位图索引条目。倘若其他修改也需要更新同样的这个位图索引条目，就会被“关在门外“。这样将大大影响并 发性，因为每个更新都有可能锁定数百行，不允许并发地更新它们的位图列。在此不是像你所想的那样锁定每一行，而是会锁定很多行。位图存储在块 （chunk）中，所以，使用前面的EMP例子就可以看到，索引键ANALYST在索引中出现了多次，每一次都指向数百行。更新一行时，如果修改了JOB 列，则需要独占地访问其中两个索引键条目：对应老值的索引键条目和对应新值的索引键条目。这两个条目指向的数百行就不允许其他会话修改，直到UPDATE 提交。java-javascript 风之境地

【推广】 免费学中医，健康全家人
原文地址：https://www.cnblogs.com/sky7034/p/2277681.html