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

bitmap indexes 的結構分析

oracle dump bitmap indexes

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

相關文章