ITPub博客

首页 > Linux操作系统 > Linux操作系统 > bitmap indexes 的结构分析

bitmap indexes 的结构分析

原创 Linux操作系统 作者:tolywang 时间:2007-06-29 00:00:00 0 删除 编辑

oracle dump bitmap indexes

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

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

1, DELETE,UPDATE,INSERT比较耗时。
2,适合low-cardinalityCOLUMN
3,得到的结果占表行数的比例较大时,也是效率较高的。
BITMAPB*TREE有很大区别,
B TREE INDEX 的每一个NODE最多有2n个值2n+1POINT,(如果LEFTRIGHT页节点都存在的话)。最少有d个值和d+1point (如果是一颗歪的树的话,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 cardinalityBITMAP所占用的空间要比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,有533LEAF节点
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle , MySQL, SAP IQ, SAP HANA, PostgreSQL, Tableau 技术讨论,希望在这里一起分享知识,讨论技术,畅谈人生 。

注册时间:2007-12-10

  • 博文量
    5595
  • 访问量
    13308343