ORA FAQ Performance Tuning Series-Does compressing indexes improve performance?

Index | performance | compression
Would compressing my indexes improve performance?
Does compressing an index 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 would compressing your indexes automatically improve PE Rformance?
Compressed indexes have been around for years-so does compressing indexes automatically improve performance?

Oracle introduced a compression option for indexes in Oracle 8.1. You can create a index as compressed, or rebuild it to compress it (although there are some restrictions about online Reb Uilds, rebuilds of partitioned indexes etc.) Typical syntax might be:
Oracle introduced the compression characteristics of indexes in Oracle 8.1. You can create a compressed index, or compress an index while rebuilding (although there are some restrictions on online rebuilding, rebuilding the partitioning index, and so on). The standard syntax is as follows:

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 the leaf blocks-whi CH tends to mean that less I/O are involved when the index was used, there is a reduced amount of buffer cache flushing, and The optimizer is likely to calculate a lower cost for using this index for range scans. (There is a tiny chance this number of branch blocks, and the index height might be reduced, too, but this is a little Unlikely).
The advantage of compression comes from the use of fewer leaf blocks for a properly compressed index-so that when the index is used it involves less i/o,buffer cache cleaning, and the optimizer may be less likely to calculate the scan price of the index range. (Even the chance of branching blocks and index heights is reduced, but this is unlikely).

But compressing indexes, especially compressing the wrong number of columns, can have negative impact on your performance. If you are compress more columns than your should, the ' compressed ' index may be larger than the uncompressed index. Use the Validate option on the index, and check view index_stats the optimum compression count. How did I know that I should compress just the ' the ' two columns of the The T1_ci_1 index? (Apart from knowing the data, which is):
However, compressing the index, especially if the strong number of compression is incorrect, can adversely affect performance. If you compress too many columns, the compressed index may be larger than the uncompressed index. Use the Validate option on the index, and then check the view index_stats find the optimal number of compresses. How do I know that only the first two columns of the index t1_ci_1 need to be compressed? (No need to know the data):

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 at 8.1, only in version 9 (possibly only 9.2). Fortunately Steve Adams has a script on he website to recommend a compression level (in the www.ixora.com.au)
Unfortunately, the two columns do not exist in 8.1 and only exist in 9 (perhaps just 9.2). Luckily, Steve Adams has a script on his site to recommend compression (reference www.ixora.com.au)

Even if you are the ' right ' number of columns compressed, there are a price to pay:the main penalties are: (a) reads and M ODS of a compressed index cost more CPU than they would (typically) for a equivalent uncompressed index (b) Execution Pat HS Change-and You have predicted the changes, and some nominally cheaper-paths may actually. For example:oracle may choose a index fast full scan instead of a index range scan because the compressed index is now Much smaller, and your setting to parameter db_file_multiblock_read_count is large; Or Oracle may choose to use a nested loop because the index's now 30% smaller where previously it ing a table scan and hash join.
Even if you get the "correct" number of compressed columns, another cost: (a) reading and changing a compressed index consumes more CPUs than an equivalent uncompressed index; (b) Implementation path changes-and you may not be aware of this change, some seemingly less costly paths may be slower. For example, because the compression index is now smaller and the parameter db_file_multiblock_read_count is larger, Oracle may choose a index fast full scan instead of the index range scan Or because the index is reduced 30%,oracle chooses to use an index and a nested loop, which is preceded by a table scan and hash join.

So-don ' t go and compress all the indexes in your schema.
So--don't compress all indexes.

Carefully about which indexes could give you significant gains, and whether your can afford some CPU loss to reduce B Uffer thrashing and I/O.
Think of that index will give you a greater performance improvement, you can withstand some CPU loss to reduce buffer cleaning and I/O.

Remember too, if the way you use a index is such this column order doesn ' t matter, then perhaps you could rearrange t The He column is to maximise the compression. The most critical point, perhaps, are that you should avoid moving a column this is typically used with a range scan toward s the front of the index.t
Also remember that if you don't care about the order of columns when you use an index, you may want to rearrange the order of columns to increase the compression rate. Perhaps the most crucial point is that you should avoid moving forward to the index a column that is typically used for range scan.

--------------------------------------------------------------------------------
The translator only retains the copyright in translation from http://www.jlcomp.demon.co.uk/faq/