Oracle BITMAP Index Structure

Index block with PCTFREE 50

select extent_id, file_id,block_id from dba_extents where segment_name='IND_BMP_50' order by extent_id

EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
0 1074 53953
1 1115 53841
2 1196 53745
3 1742 447857
4 1744 446833


SQL > alter system dump datafile 1742 block 447857

row#0[6126] flag: ------, lock: 0, len=1906
col 0; len 1; (1): 80 <-key value
col 1; len 6; (6): 2b 40 d2 29 00 08 <-Starting Rowid
col 2; len 6; (6): 2b 40 d2 c1 00 1f <-Ending Rowid
col 3; len 1886; (1886): <-Bitmap for the key value


row#1[4221] flag: ------, lock: 0, len=1905
col 0; len 1; (1): 80
col 1; len 6; (6): 2b 40 d2 c1 00 30
col 2; len 6; (6): 2b 40 d3 48 00 67
col 3; len 1885; (1885):

There are two rows in the block. Totallying their column size :
1+6+6+1886+1+6+6+1885 = 3797kb
So, block is occupying approx 50% of the block (8192kb)



Index block with PCTFREE 10

SQL> select extent_id, file_id,block_id from dba_extents where segment_name='IND_BMP_10' order by extent_id;

EXTENT_ID FILE_ID BLOCK_ID
---------- ---------- ----------
0 1074 63057
1 1115 62945
2 1196 62849
3 1742 457377
4 1744 456177
5 1786 429665
6 1809 411889


alter system dump datafile 1742 block 457377

row#0[4495] flag: ------, lock: 0, len=3537
col 0; len 1; (1): 80 <-key value
col 1; len 6; (6): b3 c6 f6 2b 00 28 <-Starting rowid
col 2; len 6; (6): b3 c6 f6 ce 00 37 <-Ending rowid
col 3; len 3517; (3517): <-Bitmap for the key value


row#1[957] flag: ------, lock: 0, len=3538
col 0; len 1; (1): 80
col 1; len 6; (6): b3 c6 f6 ce 00 48
col 2; len 6; (6): b3 c6 f7 74 00 37
col 3; len 3518; (3518):


There are two rows in the block. Totallying their column size:
1+6+6+3517+1+6+6+3518 = 7161 kb
So, block is occupying approx 90% of the block (8192kb)