索引压缩问题

关于索引压缩的研究
当单列索引和复合索引中的数据列重复项比较多的时候，可以考虑进行索引压缩。索引压缩可以在某种程度上减小索引所占空间，减小扫描索引时候的I/O，提高查询的性能。
语法：create index index_name on table_name(col1,col2 ….coln) compress n; （n>0）
不输入n的话，默认压缩所有的索引列
索引中的前n项被压缩，称做前缀。
事实上，压缩索引所能节约的空间百分比大小与压缩索引的前缀字段大小占总字段大小的百分比有关系。前缀字段所占百分比越大，则节约空间越大，压缩效果越明显；反之，则节约空间越小，压缩效果越不明显。
由此可见，使用压缩索引的前提必须是前缀列的重复项比较多，否则会对性能产生更坏的影响。
通过dump的结果就可以看到压缩索引节约空间的原因了。经过其他的一些实验还发现以下几点：
1，        仅仅该BLOCK中存在该前缀对应的记录，该前缀的说明才会在BLOCK中出现。
2，        当索引中记录增多到引起叶的分裂的时候，相同前缀的记录会尽量存储在相同的BLOCK中，即BLOCK中的记录会发生重组。
实验三：压缩索引对查询性能的影响
一般说来，因为索引压缩后所占用的空间比较小，所以在发生索引扫描的时候需要访问的索引块比较小，会提高查询的性能。

Actually I was wrong in asking you that question. Although single column compression is not commonly done, it does what it's supposed to do. Here's an extreme case (on Oracle 10gR1):

create table test (a number);
insert into test select 1 from dba_objects; -- inserted 49807 rows
create index test_ind on test (a);
exec dbms_stats.gather_index_stats('YONG', 'TEST_IND')
user_indexes shows that compress='DISABLED', leaf_blocks=98, avg_blocks_per_key=98
alter index test_ind rebuild compress;
user_indexes shows that compress='ENABLED', leaf_blocks=77, avg_blocks_per_key=77

So we reduced the size by 21 blocks.

The reason single column indexes are normally not compressed is that (1) B-tree indexes should be created when keys have high selectivity or they would be useless, (2) single column unique indexes can't be compressed, (3) bitmap indexes have very low selectivity but they can't be compressed and don't need to be (they're already very compact).

 

--------------------------------------

 

ORA FAQ 性能调整系列之——压缩索引会提高性能么？
Will compressing my indexes improve performance ?
压缩索引会提高性能么？

Author's name: Jonathan Lewis

Author's Email: [email]Jonathan@jlcomp.demon.co.uk[/email]
        Date written: 26th Feb 2003

Oracle version(s): 8.1 - 9.2

Compressed indexes have been around for a couple of years now - but will compressing your indexes automatically improve performance ?
压缩索引已经存在好几年了——那么压缩索引会自动提高性能么？

Oracle introduced a compression option for indexes in Oracle 8.1. You can create an index as compressed, or rebuild it to compress it (although there are some restrictions about online rebuilds, rebuilds of partitioned indexes etc.) Typical syntax might be:
Oracle在Oracle 8.1中引入了索引的压缩特性。你可以创建一个压缩索引，或者重建时压缩一个索引（尽管对在线重建、重建分区索引等有一些限制）。标准语法如下：

        create index t1_ci_1 on t1(col1, col2, col3, col4) compress 2;

        alter index t1_ci_1 rebuild compress 2;

The benefits of compression come from the fact that a properly compressed index uses a smaller number of leaf blocks - which tends to mean that less I/O is involved when the index is used, there is a reduced amount of buffer cache flushing, and the optimizer is likely to calculate a lower cost for using that index for range scans. (There is a tiny chance that the number of branch blocks, and the index height might be reduced, too, but that is a little unlikely).
压缩的优势来自一个恰当压缩的索引使用更少的叶块——这样当用到索引时涉及更少的I/O，buffer cache清洗量减小，优化器对index range scan代价的计算可能更低。（甚至有机会分支块数与索引高度也会减少，但这不太可能）。

But compressing indexes, especially compressing the wrong number of columns, can have negative impact on your performance. If you compress more columns than you should, the 'compressed' index may be larger than the uncompressed index. Use the validate option on the index, and check view index_stats to find out the optimum compression count. How did I know that I should compress just the first two columns of the t1_ci_1 index ? (Apart from knowing the data, that is):
但压缩索引，特别是压缩烈数不正确时，会对性能产生负面影响。如果压缩了过多的列，“压缩”了的索引可能比未压缩的索引更大。对索引使用validate 选项，然后检查视图index_stats找到最优的压缩数。我如何知道只需要压缩索引t1_ci_1的前两列？（不需要知道数据）：

        validate index t1_ci_1;

        select
        opt_cmpt_count, opt_cmpr_pctsave
        from
        index_stats;

        opt_cmpt_count opt_cmpr_pctsave
        -------------------------------
                     2               50

Unfortunately these two columns don't exist in 8.1, only in version 9 (possibly only 9.2). Fortunately Steve Adams has a script on his website to recommend a compression level (see [url]www.ixora.com.au[/url] )
不幸的是这两列在8.1中不存在，只存在于9（可能仅仅9.2）。幸运的是Steve Adams在他的站点上有一个脚本以推荐压缩度（参考www.ixora.com.au）

Even if you get the 'right' number of columns compressed, there is a price to pay: The main penalties are: (a) reads and mods of a compressed index cost more CPU than they would (typically) for an equivalent uncompressed index (b) execution paths change - and you may not have predicted the changes, and some nominally cheaper paths may actually be slower. for example: Oracle may choose an index fast full scan instead of an index range scan because the compressed index is now much smaller, and your setting for parameter db_file_multiblock_read_count is large; or Oracle may choose to use an index and do a nested loop because the index is now 30% smaller, where previously it was doing a table scan and hash join.
即使你得到了压缩列的“正确”数字，还有一个代价：主要的性能损失是：(a)读、改一个压缩索引比一个同等的未压缩索引消耗更多的CPU；(b)执行路径改变——并且你可能没有意识到这个改变，一些看似代价更低的路径可能反而慢。例如：由于压缩索引现在更小了，对参数 db_file_multiblock_read_count也较大，那么Oracle可能选择一个index fast full scan而不是index range scan；或者由于索引减小了30%，Oracle选择使用一个索引和nested loop，而之前它用表扫描和hash join。

So - don't go and compress all the indexes in your schema.
所以——不要压缩所有索引。

Think carefully about which indexes could give you significant gains, and whether you can afford some CPU loss to reduce buffer thrashing and I/O.
想好那个索引会给你较大的性能提高，你是否能够承受一些CPU损耗来降低buffer清洗和I/O。

Remember too, if the way you use an index is such that the column order doesn't matter, then perhaps you could rearrange the column order to maximise the compression. The most critical point, perhaps, is that you should avoid moving a column that is typically used with a range scan towards the front of the index.t
还要记住，若你使用索引时并不在意列的顺序，那么也许你可以重新安排列的顺序来提高压缩率。最关键的一点也许是你应当避免向索引前移一个一般用来range scan的列。