ORA FAQ 性能调整系列之——压缩索引会提高性能么？
2024-07-21 02:41:04
供稿：网友
Will comPRessing my indexes improve performance ?
压缩索引会提高性能么？
Author's name: Jonathan Lewis
Author's Email: Jonathan@jlcomp.demon.co.uk
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 www.ixora.com.au )
不幸的是这两列在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的列。
--------------------------------------------------------------------------------
本文翻译自http://www.jlcomp.demon.co.uk/faq/ 译者仅保留翻译版权
