[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

--可以发现这样修改没有问题.