[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.