mysql 存储位图_查看位图索引存储结构

环境:

OS:Red Hat Linux As 5

DB:10.2.0.4

位图索引跟普通索引不同之处是,位图索引的每个键值里记录了启始rowid和结束rowid,下面可以通过dump索引结构可以查看.

1.建表并创建bitmap索引

Create Table tb_bitmap_test

(

Id Number,

Name Varchar2(20),

type  Varchar2(2)

);

Declare

Begin

For i In 1 .. 1000 Loop

Insert Into tb_bitmap_test Values (i, i, Decode(Mod(i, 10), 0, '男', '女'));

End Loop;

End;

Create Bitmap Index inx_bitmap_n1 On tb_bitmap_test(***);

Select object_id From Dba_Objects t

Where t.owner = 'SCOTT'

And object_name='INX_BITMAP_N1'

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

OBJECT_ID

69819

2.Dump索引树

alter session set events 'immediate trace name treedump level 69819';

trace文件输出的部分内容如下:

*** 2012-07-17 14:34:07.247

*** SESSION ID:(134.23) 2012-07-17 14:34:07.247

*** CLIENT ID:() 2012-07-17 14:34:07.247

*** SERVICE NAME:(SYS$USERS) 2012-07-17 14:34:07.247

*** MODULE NAME:(sqlplus.exe) 2012-07-17 14:34:07.247

*** ACTION NAME:() 2012-07-17 14:34:07.247

----- begin tree dump

leaf: 0x100008c 16777356 (0: nrow: 2 rrow: 2)

----- end tree dump

3.Dump索引块

select dbms_utility.data_block_address_file(16777356) file_no,

dbms_utility.data_block_address_block(16777356) block_no from dual

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

FILE_NO BLOCK_NO

4       140

alter system dump datafile 4 block 140;

trace文件输出的部分内容如下:

Block header dump:  0x0100008c

Object id on Block? Y

seg/obj: 0x110bb  csc: 0x00.e625f  itc: 2  flg: E  typ: 2 - INDEX

brn: 0  bdba: 0x1000089 ver: 0x01 opc: 0

inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc

0x01   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000

0x02   0xffff.000.00000000  0x00000000.0000.00  C---    0  scn 0x0000.000e625f

Leaf block dump

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

header address 404460644=0x181b9464

kdxcolev 0

KDXCOLEV Flags = - - -

kdxcolok 0

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

kdxconco 4

kdxcosdc 0

kdxconro 2

kdxcofbo 40=0x28

kdxcofeo 7710=0x1e1e

kdxcoavs 7670

kdxlespl 0

kdxlende 0

kdxlenxt 0=0x0

kdxleprv 0=0x0

kdxledsz 0

kdxlebksz 8036

row#0[7878] flag: ------, lock: 0, len=158

col 0; len 2; (2):  c4 d0        -- utl_raw.cast_to_varchar2('c4d0') 输出:'男'col 1; len 6; (6):  01 00 00 84 00 08  -- begin rowid:AAARC6AAEAAAACEAAIcol 2; len 6; (6):  01 00 00 88 01 c7  -- end rowid:AAARC6AAEAAAACIAEzcol 3; len 137; (137):                 -- 位图编码 cf 01 04 10 40 00 01 04 10 06 ff 88 02 02 08 20 80 00 02 08 20 cf 80 00 02

08 20 80 00 02 cf 08 20 80 00 02 08 20 80 d7 02 08 20 80 00 02 08 20 cf 80

00 02 08 20 80 00 02 cf 08 20 80 00 02 08 20 80 d6 02 08 20 80 00 02 08 ff

1d 04 10 40 00 01 04 10 40 d7 01 04 10 40 00 01 04 10 cf 40 00 01 04 10 40

00 01 cf 04 10 40 00 01 04 10 40 d7 01 04 10 40 00 01 04 10 cf 40 00 01 04

10 40 00 01 ce 04 10 40 00 01 04 10

row#1[7710] flag: ------, lock: 0, len=168

col 0; len 2; (2):  c5 ae       -- utl_raw.cast_to_varchar2('c5ae') 输出:'女'col 1; len 6; (6):  01 00 00 84 00 00

col 2; len 6; (6):  01 00 00 88 01 c7

col 3; len 147; (147):

cf ff fe fb ef bf ff fe fb c9 ef 3f ff 87 02 ff fd f7 df 7f ff fd f7 cf df

7f ff fd f7 df 7f ff cf fd f7 df 7f ff fd f7 df cf 7f ff fd f7 df 7f ff fd

cf f7 df 7f ff fd f7 df 7f cf ff fd f7 df 7f ff fd f7 cf df 7f ff fd f7 df

7f ff ca fd f7 07 ff 1c fb ef bf ff fe fb ef bf cf ff fe fb ef bf ff fe fb

cf ef bf ff fe fb ef bf ff cf fe fb ef bf ff fe fb ef cf bf ff fe fb ef bf

ff fe cf fb ef bf ff fe fb ef bf cf ff fe fb ef bf ff fe fb c8 2f

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

End dump data blocks tsn: 4 file#: 4 minblk 140 maxblk 140

-- The End --