ITPub博客

首页 > 数据库 > Oracle > [20140402]关于位图索引的统计信息

[20140402]关于位图索引的统计信息

原创 Oracle 作者:lfree 时间:2014-04-04 09:30:52 0 删除 编辑

[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 level<=1e4;
Table created.

SCOTT@test01p> insert into t select rownum+1e4,'N' flag ,'TEST' name from dual connect by level<=1e4;
10000 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 level<=2e4;
Table 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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/267265/viewspace-1135861/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2616
  • 访问量
    6385945