Database Reference
In-Depth Information
INDeX COMpreSSION
One important difference between B-tree and bitmap indexes is the compression used to store the keys in the
index leaf blocks. Whereas bitmap indexes are always compressed, B-tree indexes are compressed only when
requested.
In a noncompressed B-tree index, every key is fully stored. In other words, if several keys have the same value,
the value is repeatedly stored for each key. Consequently, in nonunique indexes, it's common to have the same
value stored several times in the same leaf block. to eliminate these repeated occurrences, you can compress
the index keys by (re)building the index with the
COMPRESS
parameter and, optionally, the number of columns
that need to be compressed. For example, the
i_n123
index is composed of three columns:
n1
,
n2
, and
n3
. With
COMPRESS 1
, you specify to compress only the
n1
column; with
COMPRESS 2
, you specify to compress the
n1
and
n2
columns; and with
COMPRESS 3
, you specify to compress all three columns. When the number of columns to
be compressed isn't specified, for nonunique indexes all columns are compressed, and for unique indexes the
number of columns minus one are compressed.
Because columns are compressed from left to right, columns should be ordered by decreasing selectivity to
achieve the best compression. however, you should reorder the columns of an index only when this doesn't
prevent the query optimizer from using the index.
B-tree index compression isn't activated per default because it doesn't always reduce the size of indexes.
actually, in some situations, the index might become larger with compression! Because of this, you should
enable compression only if there's a real advantage to doing so. You have two options for checking the expected
compression ratio for a given index. First, you can build the index once without compression and then again with
compression, and then compare the size. Second, you can let the
ANALYZE INDEX
statement perform an analysis
to find out the optimal number of columns to compress and how much space can be saved with the optimal
compression. the following example shows such an analysis for the
i_n123
index. note that the output of the
analysis is written in the
index_stats
table. In this case, you're informed that by compressing two columns you
can save 17 percent of the space currently occupied by the index:
SQL> ANALYZE INDEX i_n123 VALIDATE STRUCTURE;
SQL> SELECT opt_cmpr_count, opt_cmpr_pctsave FROM index_stats;
OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
2 17
the following SQL statements show not only how to implement the compression of the
i_n123
index but also
how to check the result of the compression:
SQL> SELECT blocks FROM index_stats;
BLOCKS
----------
40
SQL> ALTER INDEX i_n123 REBUILD COMPRESS 2;
