一 : 创建分区位图索引测试
先创建一个分区表，如下：二 : 位图索引(Bitmap Index)的故事
[原文：http://blog.csdn.net/carlwu/archive/2008/04/24/2319584.aspx]
您如果熟悉Oracle数据库，我想您对Thomas Kyte的大名一定不会陌生。[www.61k.com）Tomas主持的asktom.oracle.com网站享誉Oracle界数十年，绝非幸致。最近在图书馆借到这位Oracle绝顶高手编著的《Expert Oracle Database Architecture-9i and 10g programming Techniques and Solutions》，翻阅之下，果然盛名无虚，虽然说不上字字珠玑，但作者对Oracle架构的理解和实践确实已达到出神入化的境界。如果您有时间和兴趣，强烈建议您阅读这本书。这本书最大的特点是语言生动活泼，说理清楚，几乎每讲解一个原理，作者都给出了具体实例，让人读起来毫不气闷。
另外，Thomas谦逊的态度让我非常佩服，Thomas在Oracle数据库方面工作了16年，并且参与了早期Oracle版本的开发，但他仍然谦虚地说，他每天都能从Oracle文档里学到新的东西。
下面从这本书里摘录了一些精彩片段，供您欣赏，虽然不免有断章取义之嫌。
位图索引(Bitmap Index)的故事
一日，一群Java开发者找到Tom先生，说他们新开发的系统已经上线，但性能及其低下，他们问Tom先生能不能替他们看看问题到底出在什么地方。他们告诉Tom，他们的系统采用JSP+EJB+Oracle的典型三层架构，其中EJB中的SQL是由第三方工具产生的。Tom同志一听到EJB，就知道这个系统是不能采用SQL代码跟踪的方法来进行性能调优了。于是，Tom同志告诉这些心急火燎的Java开发者，你们系统的问题肯定在浏览器到数据库之间，但具体问题出在什么地方，我需要看看你们的数据库。
于是，Tom同志远程连接到他们的测试数据库（注意不是生产数据库），查看了几个动态性能视图(V$LOCK和V$SQL)，最后终于发现了问题的所在。Tom同志发现他们的数据库中有一个位图索引(Bitmap Index)最为可疑，这个索引是建立在一个PROCESS_FLAG的字段上。PROCESS_FLAG字段表示该记录是否被处理了，可能值只有两个，一个是未处理(N)，一个是已经处理(Y)。当记录初次插入数据库时，该字段的值为N，但其它进程读取并处理那些未处理的记录(值为N的记录)后，这个字段的值就更新为Y。
Tom就问这些Java开发者，你们为什么要在这个PROCESS_FLAG字段上建立位图索引呢？
其中有一个开发者振振有词的说，这是为了提高查找速度，一旦建立了位图索引，我们的程序就能快速找到那些数值为N的记录，然后处理。随后，他又拿出一本大部头的Oracle数据库参考手册，对Tom同志说，这书上都是这么说的，对那些数值非常少的字段，比如，我们的PROCESS_FLAG字段只有两个值，就应该建立位图索引，这难道有什么问题吗？
Tom同志微微一笑，没有直接回答。只见他打开SQL Plus，连接到他的本地Oracle实例，给这群开发者演示了下面及其简单的SQL代码。
C:\Documents and Settings\carlwu>sqlplus scott/tiger@carl
SQL*Plus: Release 11.1.0.6.0 - Production on Wed Apr 23 18:15:34 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> create table t(processed_flag varchar2(1));
Table created.
SQL> create bitmap index t_idx on t(processed_flag);
Index created.
SQL> insert into t values('N');
1 row created.
SQL>
刚才那位振振有词的开发者不服气的说，这有什么，不是很正常吗？接着Tom又打开了一个SQL Plus窗口，并连接到本地数据库，键入下面的SQL语句，奇怪的是这条SQL并不执行，而是一直在等待。下面是这条SQL的一个截图：
这些Java开发者看到这里，惊讶得目瞪口呆。其中一个开发者犹豫地说，好象这个位图索引只允许一个用户操作，如果其它用户想同时操作这个索引，那他必须等第一个用户的请求处理完成，并且提交之后，才能进行，如果第一个用户不提交，那么他必须一直等待。Tom点头表示赞同，然后给他们作了一番详细的解释：
Oracle数据库的位图索引(Bitmap Index)确实是针对那些数值稀疏(low-cardinality，低基数)的字段，但是还应记住的一点是，它是针对那些值不经常改变的字段的。在实际应用中，如果某个字段的值需要频繁更新，那么就不适合在它上面创建位图索引。在位图索引中，如果你更新或插入其中一条数值为N的记录，那么相应表中数值为N的记录（可能成百上千条）全部被Oracle锁定，这就意味着其它用户不能同时更新这些数值为N的记录，其它用户必须要等第一个用户提交后，才能获得锁，更新或插入数据。
问题找到了，修正就很简单了，Tom建议这些开发者去掉了这个位图索引，然后在PROCESS_FLAG字段上建立一个函数索引，只为那些数值为N的记录建立简单的B树索引就可以了。
这些开发者回去后，按照Tom的指点，经过一番测试，终于解决了问题。
但故事并没有到此结束，这些开发者并不满足，他们给Tom写email抱怨道，Oracle数据库真“烂”，连这个简单的位图索引问题都不能处理，你看，Oracle浪费了我们大量的时间和精力调试我们的Java程序。Tom给他们回了一封email，颇有感触地对他们说：
I have encountered issues such as this many times when an application is being moved from database A to database B. When an application that worked flawlessly in database A does not work, or works in an apparently bizarre fashion, on database B, the first thought is that database B is “bad” database. The simple truth is that database B just works differently. Neither database is wrong or “bad; they are just different. Knowing and understanding how they both work will help you immensely in dealing with these issues.
(当人们把一个应用从一种数据库迁移到另一种数据库时，他们常常抱怨同样的问题。本来这个应用程序在数据库A上运行得很好，当迁移到数据库B时，就出问题了。于是他们就认定，数据库B真烂。但事实并非如此，这只是因为数据库B的工作方式和原理不同于数据库A而已。世界上没有哪个数据库是“烂”数据库，关键是我们必须深入了解该数据库的架构和特点，这样才能避免这类问题。如果您理解位图索引的适用条件，您还会说Oracle是一个很“烂“的数据库吗？)
最后，Tom乘机建议他们，如果你们愿意，我可以给你们做一次简单的为期3天的培训。这些Java程序员听从了Tom同志的建议，经过了3天的培训后，他们对Oracle能做的事情表示吃惊，他们纷纷表示，“我真傻，原来Oracle不适合建立临时表呀，你看我的程序老是在那里删除和创建临时表。”，“要是我用了物化视图(Materalized View)，我的数据备份代码就异常简单了。”，“我还不知道connect by有这么强大的功能呢！”。
三 : 位图索引原理分析
位图索引
位图索引
一.什么是位图索引 我们目前大量使用的索引一般主要是B*Tree索引,在索引结构中存储着键值和键值的RowID,并且是一一对应的.
而位图索引主要针对大量相同值的列而创建(例如：类别，操作员，部门ID,库房ID等),
索引块的一个索引行中存储键值和起止Rowid,以及这些键值的位置编码,
位置编码中的每一位表示键值对应的数据行的有无.一个位图索引块可能指向的是几十甚至成百上千行数据的位置.
这种方式存储数据,相对于B*Tree索引,占用的空间非常小,创建和使用非常快.
当根据键值查询时,可以根据起始Rowid和位图状态,快速定位数据. 当根据键值做and,or或 in(x,y,..)查询时,直接用索引的位图进行或运算,快速得出结果行数据.
当select count(XX) 时,可以直接访问索引就快速得出统计数据. 创建语法很简单,就是在普通索引创建的语法中index前加关键字bitmap即可,例如:
create bitmap index H病人挂号记录_ix_执行人 on H病人挂号记录(执行人);
二.位图索引的特点
1.Bitmap索引的存储空间
相对于B*Tree索引,位图索引由于只存储键值的起止Rowid和位图,占用的空间非常少.
bitmap的空间占用主要根以下4个因素相关:
a.表的总记录数
b.索引列的键值多少,列的不同值越少,所需的位图就越少. c.操作的类型,批量插入比单条插入所需的位图要少得多,8i,9i下是这样的,10G则没有这种区别,详见后面的分析.
d.索引列相同键值的物理分布,8i,9i中,不同块上的数据,相同的键值,会建立不同的位图行(段)来表示
注:本文提到的8i,9i,10g,我试验的环境是8.1.7,9.2.0.5,10.2
位图索引 位图索引原理分析
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相互操作都会发锁定。(www.61k.com）
对于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行）,所以被锁住了。（www.61k.com］
三.位图索引的适用场合 1.位图索引是Oracle数据库在7.3版本中加入的,8i,9i企业版和个人版支持,标准版不支持.
2.基于规则的优化器无法使用Bitmap索引
3.适应于有大量重复值的列查询
4.对于8i,9i版本，不适用于单行插入,适用于批量插入的数据, 因为单行插入时，相同键值，每插入8行就会生成一行索引块中的位图段,即使相同的值.
而批量插入时，相同键值只生成一个位图段.
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.一次插入一行,插入多行后,一次提交;
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
61阅读提醒您本文地址：
位图索引 位图索引原理分析
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,'张Union All Select 3,'G000003',1,'张Union All Select 4,'G000004',1,'张Union All Select 5,'G000005',1,'张Union All 1' From dual 1' From dual 1' From dual 1' From dual 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对位图索引的存储进行了优化,一个键值在索引块中只有一个位图
注意,其中有些结论并不是完全正确的,可以自己实验证明,另外,该文涉及的实验没有标明Oracle版本,不同的版本,结果有差异.
61阅读提醒您本文地址：
本文标题：位图索引-创建分区位图索引测试61阅读| 精彩专题| 最新文章| 热门文章| 苏ICP备13036349号-1