bitmap indexes 的结构分析

oracle dump bitmap indexes

BITMAP和B*TREE INDEX的结构是相似的，这里主要分析下BITMAP的结构，和表达一个观点：不是LOW CANDIDATE的列都是适合做BITMAP的。

众所周知,BITMAP的一些特性是

1, DELETE,UPDATE,INSERT比较耗时。

2，适合low-cardinality的COLUMN。

3，得到的结果占表行数的比例较大时，也是效率较高的。

BITMAP和B*TREE有很大区别，

B TREE INDEX 的每一个NODE最多有2n个值2n+1个POINT，（如果LEFT，RIGHT页节点都存在的话）。最少有d个值和d+1个point （如果是一颗歪的树的话，n,d指的是树的层数）。树的LEFT是小的值。

BITMAPINDEX的并发性很不好，甚至两个会话作相同的INSERT都会发生DEADLOCK，如果一个SESSION批量DML则表现很好,实验如下

create table wwm nologging as

Select rownum id,mod(rownum,10) btree_col,mod(rownum,10) bitmap_col, rpad(‘x’,200) padding from all_objects where rownum<3000;

此时，表中有2999条记录，并且btree_col,bitmap_col里的值都是0—9，

分别建立两个索引

SQL> create index wwm_tree on wwm(btree_col);

SQL> create bitmap index wwm_bit on wwm(bitmap_col);

本库是ORACLE 9 数据库的块大小是8k

analyze index wwm_tree VALIDATE STRUCTURE ;

analyze table wwm estimate statistics

1* select blocks,LF_BLKS from index_stats where name='WWM_TREE'

BLOCKS LF_BLKS

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

16 6

select blocks,LF_BLKS from index_stats where name='WWM_BIT'

BLOCKS LF_BLKS

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

16 1

1 select segment_name||' '||file_id||' '||tablespace_name||' '||block_id||' '||blocks from dba_extents where segment_name='WWM_TREE' or segment_name='WWM_BIT'

SEGMENT_NAME||''||FILE_ID||''||TABLESPACE_NAME||''||BLOCK_ID||''||BLOCKS

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

WWM_TREE 14 DATA04 201 16

WWM_BIT 14 DATA04 217 16

同时，也可以做实验，通过以上SQL看看是什么对index的大小有影响，结果是只有COLUMN的长度和ROWS会有影响，而是否LOW cardinality不会有影响，将BITMAP INDEX的列用SEQUENCE代替后大小几乎没有差别。由于本库的EXTENT较大，不在这里举例了。大家还可以看到LOW cardinality的BITMAP所占用的空间要比B*TREE小很多。

1* select object_id||' '||Object_name from dba_objects where object_name='WWM_TREE' or objecT_name='WWM_BIT'

OBJECT_ID||''||OBJECT_NAME

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

32099 WWM_BIT

32098 WWM_TREE

DUMP BLOCK的 数据结构出来分析

alter system dump datafile 14 block 201 ;

alter system dump datafile 14 block 217 ;

DUMP OBJECT 的数据结构出来分析

SQL> alter session set events 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 32098'

SQL> alter session set events 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 32099’

查看DUMP文件

<<文件头>>

/usr/sap3/oracle/admin/SIDDB/udump/siddb_ora_23096.trc

Oracle9i Enterprise Edition Release 9.2.0.1.0 - 64bit Production

With the Partitioning, OLAP and Oracle Data Mining options

JServer Release 9.2.0.1.0 - Production

ORACLE_HOME = /usr/sap3/oracle/product/920

System name: HP-UX

…………………………………………

*** SESSION ID:(11.3039) 2006-10-31 17:22:15.378

〈〈alter system dump datafile 14 block 201 ;〉〉 的结果

Start dump data blocks tsn: 14 file#: 14 minblk 201 maxblk 201

buffer tsn: 14 rdba: 0x038000c9 (14/201)

scn: 0x0000.0007b8f6 seq: 0x02 flg: 0x00 tail: 0xb8f62002

frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK

Dump of First Level Bitmap Block

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

nbits : 2 nranges: 1 parent dba: 0x038000ca poffset: 0

unformatted: 6 total: 16 first useful block: 3

owning instance : 1

instance ownership changed at 10/31/2006 16:53:57

Last successful Search 10/31/2006 16:53:57

Freeness Status: nf1 0 nf2 1 nf3 0 nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 3

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Highwater:: 0x038000d3 ext#: 0 blk#: 10 ext size: 16

#blocks in seg. hdr's freelists: 0

#blocks below: 10

mapblk 0x00000000 offset: 0

HWM Flag: HWM Set

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

DBA Ranges :

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

0x038000c9 Length: 16 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:25-50% free

4:FULL 5:FULL 6:FULL 7:FULL

8:FULL 9:FULL 10:unformatted 11:unformatted

12:unformatted 13:unformatted 14:unformatted 15:unformatted

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

End dump data blocks tsn: 14 file#: 14 minblk 201 maxblk 201

〈〈alter system dump datafile 14 block 217 〉〉的结果

*** 2006-10-31 17:23:20.602

Start dump data blocks tsn: 14 file#: 14 minblk 217 maxblk 217

buffer tsn: 14 rdba: 0x038000d9 (14/217)

scn: 0x0000.0007b904 seq: 0x02 flg: 0x00 tail: 0xb9042002

frmt: 0x02 chkval: 0x0000 type: 0x20=FIRST LEVEL BITMAP BLOCK

Dump of First Level Bitmap Block

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

nbits : 2 nranges: 1 parent dba: 0x038000da poffset: 0

unformatted: 12 total: 16 first useful block: 3

owning instance : 1

instance ownership changed at 10/31/2006 16:54:13

Last successful Search 10/31/2006 16:54:13

Freeness Status: nf1 0 nf2 1 nf3 0 nf4 0

Extent Map Block Offset: 4294967295

First free datablock : 3

Bitmap block lock opcode 0

Locker xid: : 0x0000.000.00000000

Highwater:: 0x038000dd ext#: 0 blk#: 4 ext size: 16

#blocks in seg. hdr's freelists: 0

#blocks below: 1

mapblk 0x00000000 offset: 0

HWM Flag: HWM Set

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

DBA Ranges :

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

0x038000d9 Length: 16 Offset: 0

0:Metadata 1:Metadata 2:Metadata 3:25-50% free

4:unformatted 5:unformatted 6:unformatted 7:unformatted

8:unformatted 9:unformatted 10:unformatted 11:unformatted

12:unformatted 13:unformatted 14:unformatted 15:unformatted

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

End dump data blocks tsn: 14 file#: 14 minblk 217 maxblk 217

*** 2006-10-31 18:19:00.639

----- begin tree dump

〈〈alter session set events 'IMMEDIATE TRACE NAME TREEDUMP LEVEL 32098'〉〉的结果

branch: 0x38000cc 58720460 (0: nrow: 6, level: 1) ---branch block指向两个LEAF NODE

leaf: 0x38000cd 58720461 (-1: nrow: 533 rrow: 533) --leaf block，有533对LEAF节点

Leaf block dump

===============

header address 13835058057859399780=0xc0000000999d8064

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

kdxcoopc 0x80: opcode=0: iot flags=--- is converted=Y

kdxconco 2

kdxcosdc 0

kdxconro 533

kdxcofbo 1102=0x44e

kdxcofeo 1919=0x77f

kdxcoavs 817

kdxlespl 0

kdxlende 0

kdxlenxt 58720462=0x38000ce

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8016

row#0[8005] flag: -----, lock: 0

col 0; len 1; (1): 80

col 1; len 6; (6): 03 80 00 1c 00 09

row#1[7994] flag: -----, lock: 0

col 0; len 1; (1): 80

col 1; len 6; (6): 03 80 00 1c 00 13

…………………………………………….

row#532[1919] flag: -----, lock: 0

col 0; len 2; (2): c1 02

col 1; len 6; (6): 03 80 00 76 00 14

----- end of leaf block dump -----

leaf: 0x38000ce 58720462 (0: nrow: 511 rrow: 511)

Leaf block dump

===============

header address 13835058057859653732=0xc000000099a16064

……………………………………………………

row#0[8004] flag: -----, lock: 0

col 0; len 2; (2): c1 02

col 1; len 6; (6): 03 80 00 76 00 1e

row#1[7992] flag: -----, lock: 0

col 0; len 2; (2): c1 02

col 1; len 6; (6): 03 80 00 77 00 07

。。。。。。。。。。。。。。。。。。。。。。。

row#510[1884] flag: -----, lock: 0

col 0; len 2; (2): c1 04

col 1; len 6; (6): 03 80 00 5a 00 17

----- end of leaf block dump -----

leaf: 0x38000cf 58720463 (1: nrow: 511 rrow: 511)

Leaf block dump

…………………………………………………………………….

header address 13835058057859620964=0xc000000099a0e064

……………………………………………………………..

row#0[8004] flag: -----, lock: 0

col 0; len 2; (2): c1 09

col 1; len 6; (6): 03 80 00 65 00 05

row#1[7992] flag: -----, lock: 0

col 0; len 2; (2): c1 09

col 1; len 6; (6): 03 80 00 65 00 0f

………………………………………………………………….

row#8[7908] flag: -----, lock: 0

col 0; len 2; (2): c1 09

col 1; len 6; (6): 03 80 00 67 00 13

row#9[7896] flag: -----, lock: 0

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/35489/viewspace-84725/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/35489/viewspace-84725/