Oracle索引3--索引键压缩

Oracle索引3--索引键压缩

索引键压缩的是可以减小索引的大小，从而减少IO量，使扫描索引时效率更高，压缩的条件是索引的前导列或者前几个索引字段是有重复的(当然这个索引肯定是多键值索引)。比如我在这篇博客中创建的索引：http://blog.csdn.net/chuan_day/article/details/60606480。在记录行数比较大的情况下，索引键压缩是提高SQL语句性能的很有效的手段。

下面通过例子来实现对比：

         创建表t11:create table t11 as select * from dba_objects;

         创建索引：create index t11_idx on t11(owner,object_type,object_name);这里owner的重复很高因为比较数据库中用户数不会很多，对象类型也不会很多，而对象名称那就基本上不会一致。

         在这篇博客中我已经解释了如何使用index_stats视图，解释了几个比较重要的字段：http://blog.csdn.net/chuan_day/article/details/60606480

         通过以下几个例子可以很清晰的看到索引键压缩减少的空间量，其实也就是访问索引时IO的减少。

1、不压缩的情况下，记录在idx_stats表中

     analyze index T11_IDX validate structure;
     

     create table idx_stats as 
select 'nocompressed' compress_,
       height,
       lf_blks,
       t.br_blks,
       t.btree_space,
       t.opt_cmpr_count,
       t.opt_cmpr_pctsave
  from index_stats t;

2、压缩第一个字段

drop index t11_idx;
create index t11_idx on t11(OWNER, OBJECT_TYPE, OBJECT_NAME) compress 1;
analyze index T11_IDX validate structure;
insert into idx_stats
select 'compressed 1' compress_,
       height,
       lf_blks,
       t.br_blks,
       t.btree_space,
       t.opt_cmpr_count,
       t.opt_cmpr_pctsave
  from index_stats t;

3、压缩前两个字段

drop index t11_idx;
create index t11_idx on t11(OWNER, OBJECT_TYPE, OBJECT_NAME) compress 2;
analyze index T11_IDX validate structure;

insert into idx_stats
select 'compressed 2' compress_,
       height,
       lf_blks,
       t.br_blks,
       t.btree_space,
       t.opt_cmpr_count,
       t.opt_cmpr_pctsave
  from index_stats t;

4、压缩三个字段

drop index t11_idx;
create index t11_idx on t11(OWNER, OBJECT_TYPE, OBJECT_NAME) compress 3;
analyze index T11_IDX validate structure;


insert into idx_stats
select 'compressed 3' compress_,
       height,
       lf_blks,
       t.br_blks,
       t.btree_space,
       t.opt_cmpr_count,
       t.opt_cmpr_pctsave
  from index_stats t;

5、查看idx_stats表

SQL> select * from idx_stats;
COMPRESS_        HEIGHT    LF_BLKS    BR_BLKS BTREE_SPACE OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
------------ ---------- ---------- ---------- ----------- -------------- ----------------
nocompressed          3        591          4     4760128              2               28
compressed 1          3        524          4     4222032              2               19
compressed 2          3        422          3     3398408              2                0
compressed 3          3        662          4     5325480              2               35

结果很明显了在不压缩的情况下索引在b-tree中是4760128，OPT_CMPR_COUNT=2是Oracle建议使用compress 2压缩 ，OPT_CMPR_PCTSAVE的值是Oracle建议使用conpress=2压缩后能节约空间的百分比例。

四条数据很明显能得出结论compress=2时索引时最节约空间的，压缩3个字段时反而更大了，因为object_name字段时基本不重复的所以Oracle无法压缩这个索引，这其实没有节约空间的，多出的空间是压缩时需要4个字节的成本，因此反而更大了。

使用analyze index T11_IDX validate structure能判断是否压缩索引，是比较有用的调优手段，IO的开销往往是SQL语句性能的瓶颈，因此能省多少就省多少，至少我是这么干的。