[20150203]关于位图索引1.txt

--许多人知道在oltp系统不适合使用位图索引.它的索引的记录结构如下是:
字段0:键值
字段1:开始rowid
字段2:结束rowid
字段3:位图信息,指示那行记录,位图1=>表示存在.位图0=>表示不存在.

--但是字段4的位图信息,介绍的资料太少,我自己看了链接http://juliandyke.com/Presentations/BitmapIndexInternals.ppt,做一个简单探究.

1.建立测试环境:
SCOTT@test> @ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.3.0     Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

create table t(id number , name varchar2(10), status varchar2(1));
insert into t select rownum-1 id,dbms_random.string('X',10) c20,decode(mod((rownum-1),8),0,'Y','N') c1 from dual connect by levelcommit ;
create bitmap index ib_t_status on t(status);

SCOTT@test> select owner,segment_name,header_file,header_block  from dba_segments where owner=user and segment_name='IB_T_STATUS';
OWNER      SEGMENT_NAME         HEADER_FILE HEADER_BLOCK
---------- -------------------- ----------- ------------
SCOTT      IB_T_STATUS                    4          530

SCOTT@test> select rowid,t.* from t where rownumROWID                      ID NAME                 S
------------------ ---------- -------------------- -
AABI9IAAEAAAACnAAA          0 29SZ9KBWV6           Y
AABI9IAAEAAAACnAAB          1 OZK4INU5JX           N
AABI9IAAEAAAACnAAC          2 XZJFVIADKN           N
AABI9IAAEAAAACnAAD          3 Y2ETB9WP8E           N
AABI9IAAEAAAACnAAE          4 VW70WBUHP1           N

SYS@test> select dump('Y',16) from dual ;

DUMP('Y',16)
----------------
Typ=96 Len=1: 59

SYS@test> select dump('N',16) from dual ;
DUMP('N',16)
----------------
Typ=96 Len=1: 4e

SCOTT@test> @lookup_rowid AABI9IAAEAAAACnAAA
    OBJECT       FILE      BLOCK        ROW DBA                  TEXT
---------- ---------- ---------- ---------- -------------------- ----------------------------------------
    298824          4        167          0 4,167                alter system dump datafile 4 block 167 ;

2.转储一下位图索引结构,我的索引很小,应该仅仅1个根节点.就是在HEADER_BLOCK+1的块地址.

alter system checkpoint ;
alter system dump datafile 4 block 531 ;

Block header dump:  0x01000213
Object id on Block? Y
seg/obj: 0x48f49  csc: 0x02.cb4b0cd8  itc: 2  flg: E  typ: 2 - INDEX
     brn: 0  bdba: 0x1000210 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 0x0002.cb4b0cd8
Leaf block dump
===============
header address 182927745636=0x2a9757e264
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 7976=0x1f28
kdxcoavs 7936
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8032
row#0[8004] flag: ------, lock: 0, len=28
col 0; len 1; (1):  4e
col 1; len 6; (6):  01 00 00 a7 00 00
col 2; len 6; (6):  01 00 00 a7 00 3f
col 3; len 9; (9):  cf fe fe fe fe fe fe fe fe
row#1[7976] flag: ------, lock: 0, len=28
col 0; len 1; (1):  59
col 1; len 6; (6):  01 00 00 a7 00 00
col 2; len 6; (6):  01 00 00 a7 00 3f
col 3; len 9; (9):  cf 01 01 01 01 01 01 01 01
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 531 maxblk 531

--0x59 = 'Y',也就是键值.
--col 1; len 6; (6):  01 00 00 a7 00 00,也就是开始rowid.除去后面2个字节(相当于行号).记录就是数据块的dba地址.
--col 2; len 6; (6):  01 00 00 a7 00 3f,也就是结束rowid. 003f相当与行号=>63.
SYS@test>  @dfb 010000a7
    RFILE#     BLOCK#
---------- ----------
         4        167

TEXT
----------------------------------------
alter system dump datafile 4 block 167 ;

--看看col 3,col 3; len 9; (9):  cf 01 01 01 01 01 01 01 01,不看第1个字段,很容易知道推出里面的01对应的就是行号等于0,8,16,24,32,40,48,56,8条记录.
--前面键值'N',col 3; len 9; (9):  cf fe fe fe fe fe fe fe fe,也可以相互印证.

SCOTT@test> select rowid,t.* from t where status='Y';

ROWID                      ID NAME                 S
------------------ ---------- -------------------- -
AABI9IAAEAAAACnAAA          0 29SZ9KBWV6           Y
AABI9IAAEAAAACnAAI          8 J5O79GZ6VN           Y
AABI9IAAEAAAACnAAQ         16 8PABJTM5T7           Y
AABI9IAAEAAAACnAAY         24 DKJYCAVC3V           Y
AABI9IAAEAAAACnAAg         32 1SR22OTPZD           Y
AABI9IAAEAAAACnAAo         40 1KOUBWW5W2           Y
AABI9IAAEAAAACnAAw         48 HMWDKMEA84           Y
AABI9IAAEAAAACnAA4         56 SKZ1RMZ8QF           Y
8 rows selected.

--如果我执行如下:
SCOTT@test> select * from t where mod((id),8)=1;
        ID NAME                 S
---------- -------------------- -
         1 OZK4INU5JX           N
         9 3307PUWGHQ           N
        17 SMFXYCJ4M5           N
        25 W61KU1QGC5           N
        33 BWHA1ZAACJ           N
        41 QUI9MT6TPI           N
        49 X5BGM6XBPQ           N
        57 4IT8EZZHTF           N

8 rows selected.

update t set status='Y' where mod((id),8)=1;
commit;

--这样索引的col3应该变成cf 03 03 03 03 03 03 03 03.看看结果是否正确?

SCOTT@test> alter system checkpoint ;
System altered.

SCOTT@test> alter system dump datafile 4 block 531 ;
System altered.

row#0[7948] flag: ------, lock: 2, len=28
col 0; len 1; (1):  4e
col 1; len 6; (6):  01 00 00 a7 00 00
col 2; len 6; (6):  01 00 00 a7 00 3f
col 3; len 9; (9):  cf fc fc fc fc fc fc fc fc
row#1[7920] flag: ------, lock: 2, len=28
col 0; len 1; (1):  59
col 1; len 6; (6):  01 00 00 a7 00 00
col 2; len 6; (6):  01 00 00 a7 00 3f
col 3; len 9; (9):  cf 03 03 03 03 03 03 03 03
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 531 maxblk 531

--看到结果与推测一致.看的时候相当于03(二进制00000011)从右向左看.
--剩下的第1个字节:cf表示什么呢? 看下1篇blog.