教你如何成为Oracle 10g OCP - 第九章对象管理(10) - 位图(Bitmap)索引

最新推荐文章于 2024-06-09 10:01:02 发布

转载最新推荐文章于 2024-06-09 10:01:02 发布 · 131 阅读

9.2.6 位图(Bitmap)索引

建立bitmap索引的语法：
create bitmap index idx_emp_gender on emp(gender) ;

位图索引是另外一个索引类型，组织形式和b-tree索引相同，也是一颗平衡树，区别
在于叶子节点里存放索引条目的方式不同。前面知道，B树索引的叶子节点对于表里
的每个数据行，如果索引列的值不为空，则会为这个记录行在叶子节点中维护一个
对应的索引条目。而位图索引则不是这样，叶子节点中存放的索引条目如下：
http://space.itpub.net/9842/viewspace-343286

位图索引非常适用于DSS及数据仓库系统，他们可以使用较少的基数(唯一键数目，
比如男和女，基数为2)列，访问非常大的表，尽管位图索引最多可以达到30个列，
但是一般都只用于少量的列。对于那些有较低基数的列要使用位图索引。位图索引
对于低基数(少量不同值)的列来说非常快，这是因为索引的大小相对于B树索引来说
小很多，因为这些索引是低基数的B树索引。

在表上放置单独的位图索引意义不大，位图索引的作用来源于与许多其他位图索引
的结合，只有当我们执行布尔运算AND和OR的时候，它们才开始为我们产生效益，即
只有许多列具有索引的时候，用户才能够有效的利用它们。由于Bitmap索引首先不
应该在有大量DML表中使用(高并发)，所以不会因为有多个Bitmap索引带来很大的性
能问题。位图索引仅仅存在于Oracle企业版本中。

----------------------------------------------------------
注意：读这一节之前，需要复习一下rowid的结构。
例子： OOOOOOFFFBBBBBBRRR

其中：
OOOOOO：六位表示data object id，根据data object id可以确定segment。
FFF：三位表示相对文件号。根据该相对文件号可以得到绝对文件号，从而确定datafile。
BBBBBB：六位表示data block number。这里的data block number是相对于datafile
的编号，而不是相对于tablespace的编号。
RRR：三位表示row number(行号), 这里的行号是表示行在block中存储的位置，
它与rownum是完全不同的两个概念。行号越大的记录在block中存放的位置越靠后，
但并非越晚插入的记录行号越大。oracle在一个block中插入数据时，总是优先考
虑block前面的空闲空间的。

----------------------------------------------------------

bitmap联合索引及单独索引 ---

那么如果是WHERE后查询有多个条件都适合建立bitmap索引，是建立联合索引好呢，
还是建立单独索引好？

比如A有x种值，B有y种值，C有z种值, 空间使用量大致应该是
联合索引： x*y*z
单独索引： x+y+z
一般来讲第一种空间会大一些。从空间上来讲，用单独索引要要一些。

从bitmap的特性来说，对于A B C的联合查询仍然建议建立3个单独的位图索引。
因为位图索引的BITMAP AND/OR效率很高，不需要去建立联合索引提升效率，建
立复合索引意味着你人为构造一个选择度很高的键值，这与BITMAP索引的特性
冲突。

位图索引的结构 ---

在表上建立索引后，叶子节点的位图索引条目中还包含表里第一条记录所对应的rowid
及最后一条记录所对应的rowid, 所以条目的最后一部分则是由多个bit位组成的bitmap,
每个bit位就对应一条记录。格式：

bitmap叶子节点的结构大概如下(有多少记录，bitmap就有多少位)：

.....

例如：
Key     start_rowid    end_rowid      理论上的bitmap        转储文件的bitmap
<01     00c01ce4.0    00c01ce4.0017   00100110000110000010 >    ca 64 18 04
<02     00c01ce4.0    00c01ce4.0017   01000001010000110100 >    ca 82 c2 02
<03     00c01ce4.0    00c01ce4.0017   10011000101001001001 >    ca 19 25 09

其实dump出来的start-rowid及end-rowid分别如 srid=00c01ce4.0 erid=00c01ce4.17，
左边的表示文件号（从左边第一个字节开始的4个字节表示）和数据块号（从左边第五个
字节开始的4个字节表示），点右边表示数据块里的行号。

bitmap字符长度与start-rowid和end-rowid之间包含的rowid数量(表的数据行)一
样，每个块的大小是8192*8bit*90%*87%(如果数据块大小为8K，pctfree=10%, 块
头部占用一些)，那么一个bitmap索引的叶子节点大概能索引51314 条记录。

bitmap中没有直接记录rowid, 而是记录了一个位图, 位图中的每一个bit位都对应一
个rowid, 之间有转换关系的.所以用bitmap索引的时候你可以在执行计划里看到 bitmap
to rowid 这样的步骤.

当我们发出where c1='01'这样的SQL时，oracle会搜索01所在的索引条目，然后
扫描该索引条目中的bitmap里的所有bit位。第一个bit位是1，表示第一条上的c1
的值是01，于是返回第一条记录所在的rowid(根据该索引条目里记录的start rowid
加上行号得到该记录所在的rowid), 第二个bit位为0, 则说明第二条记录上的c1值不
为01,依此类推。特别注意，如果索引列为空，也会在位图索引中记录，对应的bit
位为0 ；

如果索引列上不同值的个数比较少的时候，比如对于性别列（男或女）等，则使用位
图索引会比较好，因为它对空间的占用非常少(因为都是用bit位来表示表里的数据行)，
从而在扫描索引的时候，扫描的索引块的个数也比较少。可以试想一下，如果在列的
不同值非常多的列上，比如主键列上，创建位图索引，则产生的索引条目就等于表里
记录的条数，同时每个索引条目里的bitmap里，只有一个1，其它都是0。这样还不如
B树索引的效率高。

如果被索引的列经常被更新的话，则不适合使用位图索引。当更新位图索引所在的
列时，由于要在不同的索引条目之间修改bit位，比如将第一条记录从01变为02，
则必须将01所在的索引条目的第一个bit位改为0，再将02所在的索引条目的第一个
bit位改为1。因此，在更新索引条目的过程中，会锁定位图索引里多个索引条目。
也就是同时只能有一个用户能够更新表T，从而降低了并发性。

所以位图索引比较适合用在数据仓库系统里，不适合用在OLTP系统里。

真值表 ---

在建立bitmap索引的时候，整个表上会有DML锁定。Oracle会为索引字段列中遇到
的各个值构建 "真值表" ，比如下面的表：

ID    Mannager    Dept   Gender   etc...
----------------------------------------------
70      QS         10       M
10      RW         10       F
30      RW         30       M
50      QS         20       F
40      QS         10       M
30      RW         30       M
50      RW         20       F
60      QS         10       M

在Mananger上的真值表如下：

QS RW 对应emp表中的ID列
--------------------------------------
1 0 70
0 1 10
0 1 30
1 0 50
1 0 40
0 1 30
0 1 50
1 0 60

在Gender上的真值表如下：

M F 对应emp表中的ID列
--------------------------------------
1 0 70
0 1 10
1 0 30
0 1 50
1 0 40
1 0 30
0 1 50
1 0 60

bitmap index的使用 ---

select * from emp where gender='F' and mananger='RW' ;
当做以上语句查询时，优化器会从gender位图索引中获取F位图，从Manager
位图索引中获取RW位图，让后再在他们上面执行AND操作，具体过程如下：

F 01010010
RW 01100110
------------------------------
AND 01000010 (结果)

根据该索引条目里记录的start rowid加上行号(块中行号)得到该记录所在的rowid，
然后可以找到这些记录，

Dump 位图索引 ---

SQL> create table t_bitmap_test as
2 select rownum as id,trunc(dbms_random.value(1,4)) as bitcol
3 from dba_objects where rownum<=20;
SQL> select * from t_bitmap_test;

        ID     BITCOL
---------- ----------
         1          3
         2          2
         3          1
         4          3
         5          3
         6          1
         7          1
         8          2
         9          3
        10          2
        11          3
        12          1
        13          1
        14          3
        15          2
        16          2
        17          3
        18          2
        19          1
        20          3

SQL> connect hr/hr
已连接。
SQL> alter session set events '10608 trace name context forever, level 10';
会话已更改。

SQL> create bitmap index idx_t_bitmap_test on t_bitmap_test(bitcol);
索引已创建。

SQL> alter session set events '10608 trace name context off';
会话已更改。

SQL> select object_id from user_objects where object_name='IDX_T_BITMAP_TEST';

OBJECT_ID
----------
24499

SQL> alter session set events 'immediate trace name TREEDUMP level 24499';
会话已更改。

10608事件用来跟踪创建bitmap索引的过程。而TREEDUMP则用来转储索引的
树状结构。打开转储出来的文件：

*** SESSION ID:(7.13) 2008-06-10 14:33:46.000
qerbiARwo: bitmap size is 8168
qerbiIPI default pctfree=10
qerbiIPI length=0
qerbiAllocate pfree=127 space=8168
qerbiStart first start
qerbiRop: rid=00c01ce4.0000, new=Y , key: (2): c1 04
qerbiCmpSz notfound pctfree=10
qerbiCmpSz adjblksize=7351 length=0
qerbiRop keysize=4 maxbm=3531
kdibcoinit(3116714): srid=00c01ce4.0000
qerbiRop: rid=00c01ce4.0001, new=Y , key: (2): c1 03
kdibcoinit(3116698): srid=00c01ce4.0001
qerbiRop: rid=00c01ce4.0002, new=Y , key: (2): c1 02
kdibcoinit(311661c): srid=00c01ce4.0002
qerbiRop: rid=00c01ce4.0003, new=N, key: (2): c1 04
qerbiRop: rid=00c01ce4.0004, new=N, key: (2): c1 04
qerbiRop: rid=00c01ce4.0005, new=N, key: (2): c1 02
qerbiRop: rid=00c01ce4.0006, new=N, key: (2): c1 02
qerbiRop: rid=00c01ce4.0007, new=N, key: (2): c1 03
qerbiRop: rid=00c01ce4.0008, new=N, key: (2): c1 04
qerbiRop: rid=00c01ce4.0009, new=N, key: (2): c1 03
qerbiRop: rid=00c01ce4.000a, new=N, key: (2): c1 04
qerbiRop: rid=00c01ce4.000b, new=N, key: (2): c1 02
qerbiRop: rid=00c01ce4.000c, new=N, key: (2): c1 02
qerbiRop: rid=00c01ce4.000d, new=N, key: (2): c1 04
qerbiRop: rid=00c01ce4.000e, new=N, key: (2): c1 03
qerbiRop: rid=00c01ce4.000f, new=N, key: (2): c1 03
qerbiRop: rid=00c01ce4.0010, new=N, key: (2): c1 04
qerbiRop: rid=00c01ce4.0011, new=N, key: (2): c1 03
qerbiRop: rid=00c01ce4.0012, new=N, key: (2): c1 02
qerbiRop: rid=00c01ce4.0013, new=N, key: (2): c1 04
kdibcoend(3116714): erid=00c01ce4.0017status=3
qerbiCon: key: (2): c1 04
srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4): ca 19 25 09
kdibcoend(3116698): erid=00c01ce4.0017status=3
qerbiCon: key: (2): c1 03
srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4): ca 82 c2 02
kdibcoend(311661c): erid=00c01ce4.0017status=3
qerbiCon: key: (2): c1 02
srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4): ca 64 18 04

这一段是创建bitmap索引的过程。我们先把被索引的列的值换算成十六进制：

SQL> select dump(3),dump(2),dump(1) from dual;

DUMP(3) DUMP(2) DUMP(1)
------------------ ------------------ ------------------
Typ=2 Len=2: 193,4 Typ=2 Len=2: 193,3 Typ=2 Len=2: 193,2

4、3、2对应的十六进制则是04、03、02。也就是上面转储部分显示的key部
分的键值。可以看到，oracle在创建bitmap索引时，先从第一条记录开始扫
描，取出第一条记录的键值（bitcol=3），也就是“qerbiRop: rid=00c01ce4.0000,
new=Y , key: (2): c1 04”。new=Y说明这是一个新的键值，因此会对应到
一个索引条目。扫描第二条记录时，发现bitcol=2，该键值也是一个新的键值，
因此产生一个新的索引条目，对应“qerbiRop: rid=00c01ce4.0001, new=Y ,
key: (2): c1 03”。扫描到第三条记录时，发现bitcol=1，该键值也是一个
新的键值，因此产生第三个索引条目，对应“qerbiRop: rid=00c01ce4.0002,
new=Y , key: (2): c1 02”。接下来扫描到的记录所对应的bitcol的值都是
1、2、3中的一个，因此都不会产生新的索引条目，因此它们的new都为N。

然后扫描完表里的所有记录以后，开始创建bitmap索引条目，也就是下面的部分：
qerbiCon: key: (2): c1 04
srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4): ca 19 25 09
kdibcoend(3116698): erid=00c01ce4.0017status=3
qerbiCon: key: (2): c1 03
srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4): ca 82 c2 02
kdibcoend(311661c): erid=00c01ce4.0017status=3
qerbiCon: key: (2): c1 02
srid=00c01ce4.0 erid=00c01ce4.17 bitmap: (4): ca 64 18 04

这里的srid表示start rowid，erid表示end rowid。
可以看到总共产生了3个索引条目，其key分别为：04、03、02。

这3个索引条目的start rowid和end rowid的格式分两部分，中间用点隔开，点
左边的表示文件号（从左边第一个字节开始的4个字节表示）和数据块号（从左边
第五个字节开始的4个字节表示），点右边表示数据块里的行号。这里的显示可以
看到，这20条记录都位于相同的数据块里。这里的00c0表示文件号：

SQL> select sys.pkg_number_trans.f_hex_to_dec('c')/4 file# FROM dual;

FILE#
----------
3

SQL> select sys.pkg_number_trans.f_hex_to_dec('1ce4') as blk# FROM dual;

BLK#
----------------------
7396

因此这20条记录在3号数据文件的7396号数据块里。我们可以使用dbms_rowid来验证。

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) as file#,
2 dbms_rowid.rowid_block_number(rowid) as block#
3 from t_bitmap_test;

FILE# BLOCK#
---------- ----------
3 7396

可以看到，完全符合。

每个索引条目的“bitmap : (4)”部分表示的也就是前面说到的bit位了，由1、0组成。
按照前面bitmap的理论，这20条记录所对应的三个索引条目的bitmap的样子应该为：

Key_value    start_rowid    end_rowid      理论上的bitmap       转储文件的bitmap
1          00c01ce4.0    00c01ce4.0017   00100110000110000010 ca 64 18 04
2          00c01ce4.0    00c01ce4.0017   01000001010000110100 ca 82 c2 02
3          00c01ce4.0    00c01ce4.0017   10011000101001001001 ca 19 25 09

转储文件里的bitmap如何对应到bit位呢？首先第一个字节的ca可以不考虑，暂时不知
道第一个字节是什么意思。只考虑后三个字节。比如对于key_value=3来说，19，25，09
对应的二进制为：

SQL> col c1 format a10
SQL> col c2 format a10
SQL> col c3 format a10
SQL> select sys.pkg_number_trans.f_hex_to_bin(19) as c1,
2 sys.pkg_number_trans.f_hex_to_bin(25) as c2,
3 sys.pkg_number_trans.f_hex_to_bin(09) as c3 from dual;

C1 C2 C3
---------- ---------- ----------
11001 100101 1001
2
其中不足8位的前面用0补齐，因此，C1=00011001，C2=00100101，C3=00001001
在二进制里，每个应该倒过来，从右到左排列，因此为：

C3 C2 C1
00001001 00100101 00011001

然后将它们组织为一个由多个bit位组成的bitmap的话，仍然从右到左，
依次取出每个bit位，于是我们有：100110001010010010010000。我们
可以把这个bit串与理论上的bitmap比较一下：
100110001010010010010000
10011000101001001001
很明显，除了最后多出来的4个0以外，其余部分完全一致。而最后多出
的0并不影响这个索引条目的使用。

类似的，我们可以使用相同的方法来依次验证另外两个键值，都是符合理论值的。
数据都在一个数据块里的情况比较容易理解。如果被索引的数据分布在多个数据
块里呢？

经常会有人问到，只记录start rowid和end rowid，如果被索引的记录分布在多
个数据块里，那么oracle如何根据start rowid来找到bitmap里的bit=1所对应的
记录的rowid呢？

创建一个表：
SQL> create table t_bitmap_2(id number,bitcol char(2000));
insert into t_bitmap_2 values(1,'A');
insert into t_bitmap_2 values(2,'A');
insert into t_bitmap_2 values(3,'A');
insert into t_bitmap_2 values(4,'B');
insert into t_bitmap_2 values(5,'A');
insert into t_bitmap_2 values(6,'A');
insert into t_bitmap_2 values(7,'B');
insert into t_bitmap_2 values(8,'A');
insert into t_bitmap_2 values(9,'A');
insert into t_bitmap_2 values(9,'A');
insert into t_bitmap_2 values(10,'B');
insert into t_bitmap_2 values(11,'B');
insert into t_bitmap_2 values(12,'B');
insert into t_bitmap_2 values(13,'B');
insert into t_bitmap_2 values(14,'B');
insert into t_bitmap_2 values(15,'B');
COMMIT;

获得这15条记录所在的数据块号：

SQL> select distinct dbms_rowid.rowid_relative_fno(rowid) as file#,
2 dbms_rowid.rowid_block_number(rowid) as block#
3 from t_bitmap_2;

     FILE#     BLOCK#
---------- ----------
         3       7428
         3       7429
         3       7430
         3       7431
         3       7432
         3       7433

可以知道，这15条记录分布在6个数据块里。
然后跟踪对于bitcol列上的bitmap索引的创建过程：

alter session set events '10608 trace name context forever, level 10';
create bitmap index idx_t_bitmap_2 on t_bitmap_2(bitcol);
alter session set events '10608 trace name context off';

从转储出来的文件可以看到，最终形成了两个索引条目：
……
qerbiCon: key: (2000):
41 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
……
srid=00c01d04.0 erid=00c01d08.7 bitmap: (11): c8 06 c0 44 f8 b3 01 07 f8 56 06
……
qerbiCon: key: (2000):
42 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20 20
……
srid=00c01d04.0 erid=00c01d09.7 bitmap: (12): 00 f8 56 06 f8 56 07 c0 a1 01 c0 44
*** 2008-06-10 11:21:08.000

很明显，这里的两个索引条目的start rowid和end rowid是不相同的。
键值为A的索引条目为：
srid=00c01d04.0 erid=00c01d08.7 bitmap: (11): c8 06 c0 44 f8 b3 01 07 f8 56 06
其数据块从1d04到1d08，也就是：

SQL> select sys.pkg_number_trans.f_hex_to_dec('1d04') as s_blk#,
2 sys.pkg_number_trans.f_hex_to_dec('1d08') as e_blk#
3 from dual;

S_BLK# E_BLK#
---------- ----------
7428 7432

而键值B的索引条目为：
srid=00c01d04.0 erid=00c01d09.7 bitmap: (12): 00 f8 56 06 f8 56 07 c0 a1 01 c0 44
其数据块从1d04到1d09，也就是：

SQL> select sys.pkg_number_trans.f_hex_to_dec('1d04') as s_blk#,
2 sys.pkg_number_trans.f_hex_to_dec('1d09') as e_blk#
3 from dual;

S_BLK# E_BLK#
---------- ----------
7428 7433

这个时候，
SQL> select substr(bitcol,1,1) as bitcol,dbms_rowid.rowid_block_number(rowid)
as block# from t_bitmap_2;

BI     BLOCK#
-- ----------
B        7428
A        7428
A        7428
A        7429
B        7429
B        7429
B        7430
B        7430
B        7430
A        7431
A        7431
A        7431
B        7432
A        7432
A        7432
B        7433