Will compressing my indexes 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:

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).

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 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 )

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.

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.

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