[20140402]关于位图索引的统计信息.txt
前几天,帮别人解决一些数据库问题,看awr报表很容易确定有1个表的索引使用了位图,导致在业务高峰出现阻塞情况, 当然不能简单的把
原来的位图索引删除,建立常规索引,因为查询条件存在is null的情况,我根据情况加入一个非空字段,建立复合索引,因为查询有时候涉及
这个条件,我遇到的问题是查询位图索引统计信息时遇到一些奇怪的情况,自己写一些测试来说明情况.
SCOTT@test01p> @ver
BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
SCOTT@test01p> create table t as select rownum id ,'Y' flag,'test' name from dual connect by levelTable created.
SCOTT@test01p> insert into t select rownum+1e4,'N' flag ,'TEST' name from dual connect by level10000 rows created.
SCOTT@test01p> select count(*),flag from t group by flag ;
COUNT(*) F
---------- -
10000 Y
10000 N
SCOTT@test01p> create bitmap index ib_t_flag on t(flag);
Index created.
SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,method_OPT=>'FOR ALL COLUMNS SIZE 1') ;
PL/SQL procedure successfully completed.
SCOTT@test01p> validate index ib_t_flag ;
Index analyzed.
SCOTT@test01p> @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_FLAG 2 1 2960 8000 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 8000 2960 37 1 2 0 0 0 0
SCOTT@test01p> select distinct_keys, num_rows, clustering_factor,sample_size from dba_indexes where table_name = 'T' and owner=user;
DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR SAMPLE_SIZE
------------- ---------- ----------------- -----------
2 2 2 2
--如果看索引统计信息,可以发现可以发现NUM_Rows=2,CLUSTERING_FACTOR=2,sample_size=2.而实际的记录数是2e4.
--如果了解位图索引的结构就很容易理解,做一个转储看看.
SCOTT@test01p> select header_file,header_block,bytes,blocks,extents from dba_segments where owner=user and segment_name='IB_T_FLAG';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
----------- ------------ ---------- ---------- ----------
9 258 65536 8 1
--下一块是索引的root.从上面分析也可以看出仅仅占用1块.
SCOTT@test01p> alter system dump datafile 9 block 259;
System altered.
Block header dump: 0x02400103
Object id on Block? Y
seg/obj: 0x17443 csc: 0x00.58dfc9 itc: 2 flg: E typ: 2 - INDEX
brn: 0 bdba: 0x2400100 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 0x0000.0058dfc9
Leaf block dump
===============
header address 376701028=0x16740064
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 5080=0x13d8
kdxcoavs 5040
kdxlespl 0
kdxlende 0
kdxlenxt 0=0x0
kdxleprv 0=0x0
kdxledsz 0
kdxlebksz 8036
row#0[6562] flag: -------, lock: 0, len=1474
col 0; len 1; (1): 4e
col 1; len 6; (6): 02 40 00 db 00 00
col 2; len 6; (6): 02 40 00 ff 01 af
col 3; len 1454; (1454):
cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
.... 太长截断
ff ff ff 07
row#1[5080] flag: -------, lock: 0, len=1482
col 0; len 1; (1): 59
col 1; len 6; (6): 02 40 00 bb 00 00
col 2; len 6; (6): 02 40 00 da 00 37
col 3; len 1462; (1462):
cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff ff ff cf ff ff ff ff ff ff
.... 太长截断
ff c8 0f fe 1e ff ff ff ff ff ff 03
----- end of leaf block Logical dump -----
----- end of leaf block dump -----
End dump data blocks tsn: 3 file#: 9 minblk 259 maxblk 259
SCOTT@test01p> select dump('Y',16) , dump('N',16) from dual ;
DUMP('Y',16) DUMP('N',16)
---------------- ----------------
Typ=96 Len=1: 59 Typ=96 Len=1: 4e
col 0; len 1; (1): 59
col 1; len 6; (6): 02 40 00 bb 00 00
col 2; len 6; (6): 02 40 00 da 00 37
col 3; len 1462; (1462):
--可以看出位图索引col0是键值,col1,col2 表示start rowid,end rowid.col3以位图形式表示对应的键值.
--这样可以看出行是2个,CLUSTERING_FACTOR=2.也就是位图索引的统计信息计算方式NUM_Rows,CLUSTERING_FACTOR,sample_size与普通索引不同。
--换一个方式再看看.
SCOTT@test01p> drop table t purge ;
Table dropped.
SCOTT@test01p> create table t as select rownum id ,decode(mod(rownum,3),0,'Y',1,'N',2,'A') flag,'test' name from dual connect by levelTable created.
SCOTT@test01p> create bitmap index ib_t_flag on t(flag);
Index created.
SCOTT@test01p> exec dbms_stats.gather_table_stats(null,'T',estimate_percent=>100,no_invalidate=>false,cascade=>true,method_OPT=>'FOR ALL COLUMNS SIZE 1') ;
PL/SQL procedure successfully completed.
SCOTT@test01p> validate index ib_t_flag ;
Index analyzed.
SCOTT@test01p> @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
---------- ---------- ---------- ---------- ---------- ----------- ---------- ---------- ---------- ----------- ---------- ----------- --------------- -------------
2 8 IB_T_FLAG 0 0 0 1 1 9 8032 0 0 0
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
----------------- ----------- ---------- ---------- ------------ -------------------- ---------- ------------ -------------- ----------------
0 8032 0 2 0 0 0 0
SCOTT@test01p> select distinct_keys, num_rows, clustering_factor,sample_size from dba_indexes where table_name = 'T' and owner=user;
DISTINCT_KEYS NUM_ROWS CLUSTERING_FACTOR SAMPLE_SIZE
------------- ---------- ----------------- -----------
3 3 3 3
SCOTT@test01p> select header_file,header_block,bytes,blocks,extents from dba_segments where owner=user and segment_name='IB_T_FLAG';
HEADER_FILE HEADER_BLOCK BYTES BLOCKS EXTENTS
----------- ------------ ---------- ---------- ----------
9 258 65536 8 1