查看位图索引存储结构

环境:

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:AAARC6AAEAAAACEAAI
col 2; len 6; (6):  01 00 00 88 01 c7  -- end rowid:AAARC6AAEAAAACIAEz
col 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 --