[20150205]关于位图索引7.txt
--许多人知道在oltp系统不适合使用位图索引.它的索引的记录结构如下是:
字段0:键值
字段1:开始rowid
字段2:结束rowid
字段3:位图信息,指示那行记录,位图1=>表示存在.位图0=>表示不存在.
--但是字段4的位图信息,介绍的资料太少,我自己看了链接http://juliandyke.com/Presentations/BitmapIndexInternals.ppt,做一个简单探究.
--前面的blog已经讲解了字段3的位图信息编码.
--实际上我写第1篇时一直有一个疑问:
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);
--status='Y'的col3对应的是
col 3; len 9; (9): cf 01 01 01 01 01 01 01 01
--为什么不使用单字节组 00 00 00 00 00 00 00 00 表示,而使用多字节组来表示,后面的编码更短.
--关于编码的算法oracle估计不公开,我也很难猜到.
--理论讲如果存在等效的编码,查询应该都不会有问题,自己做一个测试看看,不要在生产系统做这种事情,仅仅为了测试..
1.建立测试环境:
SCOTT@test> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- ----------------------------------------------------------------
x86_64/Linux 2.4.xx 10.2.0.4.0 Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
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),1,'Y','N') c1 from dual connect by levelcommit ;
update t set status='N' where id=1;
commit ;
SCOTT@test> select rowid,t.* from t where status='Y';
ROWID ID NAME S
------------------ ------------ -------------------- -
AAAOWEAAEAAAAGNAAJ 9 T6CTE7UJ6O Y
AAAOWEAAEAAAAGNAAR 17 WIFY6TEH4C Y
AAAOWEAAEAAAAGNAAZ 25 O9RJEGYMGX Y
AAAOWEAAEAAAAGNAAh 33 3XKLGNX6N3 Y
AAAOWEAAEAAAAGNAAp 41 IXNYY8QBBK Y
AAAOWEAAEAAAAGNAAx 49 36VZP9HSOX Y
AAAOWEAAEAAAAGNAA5 57 8QWGRDX8OV Y
7 rows selected.
SCOTT@test> @ &r/lookup_rowid AAAOWEAAEAAAAGNAAJ
OBJECT FILE BLOCK ROW DBA TEXT
------------ ------------ ------------ ------------ -------------------- ----------------------------------------
58756 4 397 9 4,397 alter system dump datafile 4 block 397 ;
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 403
2.转储索引块:
SCOTT@test> alter system checkpoint ;
System altered.
SCOTT@test> alter system dump datafile 4 block 404;
System altered.
row#0[8004] flag: ------, lock: 0, len=28
col 0; len 1; (1): 4e
col 1; len 6; (6): 01 00 01 8d 00 00
col 2; len 6; (6): 01 00 01 8d 00 3f
col 3; len 9; (9): cf ff fd fd fd fd fd fd fd
row#1[7977] flag: ------, lock: 0, len=27
col 0; len 1; (1): 59
col 1; len 6; (6): 01 00 01 8d 00 08
col 2; len 6; (6): 01 00 01 8d 00 3f
col 3; len 8; (8): ce 02 02 02 02 02 02 02
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 404 maxblk 404
--看看status='Y'的col3编码:
col 0; len 1; (1): 59
col 1; len 6; (6): 01 00 01 8d 00 08
col 2; len 6; (6): 01 00 01 8d 00 3f
col 3; len 8; (8): ce 02 02 02 02 02 02 02
--开始rowid01 00 01 8d 00 08.
--再做一次展开看看:
-- ce 拆开
-- 11 001 110
-- 11 大于192.表示Multi-Byte Groups
-- 001 表示 0个字节0
-- 110 表示7个字节长度.跟着6个字节表示位图.
--实际上如果我修改成如下也是对的,修改开始rowid为01 00 01 8d 00 00
col 0; len 1; (1): 59
col 1; len 6; (6): 01 00 01 8d 00 00
col 2; len 6; (6): 01 00 01 8d 00 3f
col 3; len 8; (8): d6 02 02 02 02 02 02 02
-- d6 拆开
-- 11 010 110
-- 11 大于192.表示Multi-Byte Groups
-- 010 表示 1个字节0 (8个0)
-- 110 表示7个字节长度.跟着6个字节表示位图.
-- 主要这样修改保持等长,这样修改量比较小,不容易出错.
3.保险期间,修改关闭数据库,我使用bvi修改.
--404*8192=3309568
$ bvi -b 3309568 -s 8192 /mnt/ramdisk/test/users01.dbf
--修改完成后要注意使用bbed重写校验信息.
BBED> set dba 4,404
DBA 0x01000194 (16777620 4,404)
BBED> sum apply
Check value for File 4, Block 404:
current = 0x14d7, required = 0x14d7
SYS@test> alter system dump datafile 4 block 404;
System altered.
row#0[8004] flag: ------, lock: 0, len=28
col 0; len 1; (1): 4e
col 1; len 6; (6): 01 00 01 8d 00 00
col 2; len 6; (6): 01 00 01 8d 00 3f
col 3; len 9; (9): cf ff fd fd fd fd fd fd fd
row#1[7977] flag: ------, lock: 0, len=27
col 0; len 1; (1): 59
col 1; len 6; (6): 01 00 01 8d 00 00
col 2; len 6; (6): 01 00 01 8d 00 3f
col 3; len 8; (8): d6 02 02 02 02 02 02 02
----- end of leaf block dump -----
End dump data blocks tsn: 4 file#: 4 minblk 404 maxblk 404
4.查询看看是否有效.
SCOTT@test> select count(*) from t where status='Y' ;
COUNT(*)
------------
7
SCOTT@test> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7a1bzunu9aqat, child number 0
-------------------------------------
select count(*) from t where status='Y'
Plan hash value: 13930037
---------------------------------------------------------------------------
| Id | Operation | Name | E-Rows | Cost (%CPU)|
---------------------------------------------------------------------------
| 1 | SORT AGGREGATE | | 1 | |
| 2 | BITMAP CONVERSION COUNT | | 32 | 1 (0)|
|* 3 | BITMAP INDEX FAST FULL SCAN| IB_T_STATUS | | |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("STATUS"='Y')
--可以发现没有问题.
SCOTT@test> validate index ib_t_status ;
Index analyzed.
SCOTT@test> @ &r/i
HEIGHT BLOCKS NAME LF_ROWS LF_BLKS LF_ROWS_LEN LF_BLK_LEN BR_ROWS BR_BLKS BR_ROWS_LEN BR_BLK_LEN DEL_LF_ROWS DEL_LF_ROWS_LEN DISTINCT_KEYS
------------ ------------ ----------- ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ ------------ --------------- -------------
1 8 IB_T_STATUS 2 1 59 7996 0 0 0 0 0 0 2
MOST_REPEATED_KEY BTREE_SPACE USED_SPACE PCT_USED ROWS_PER_KEY BLKS_GETS_PER_ACCESS PRE_ROWS PRE_ROWS_LEN OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
----------------- ------------ ------------ ------------ ------------ -------------------- ------------ ------------ -------------- ----------------
1 7996 59 1 1 2 0 0 0 0
--可以发现这样修改没有问题.
