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/ 譯者僅保留翻譯版權
