Compressing Indexes

Key compression lets you compress portions of the primary key column values in an index or index-organized table, which reduces the storage overhead of repeated values. Key compression breaks the index key into a prefix entry (the grouping piece) and a suffix entry (the unique piece). Compression is achieved by sharing the prefix entries among the suffix entries in an index block. Only keys in the leaf blocks of a B-tree index are compressed. In the branch blocks the key suffix can be truncated, but the key is not compressed. Key compression is done within an index block but not across multiple index blocks. Suffix entries form the compressed version of index rows. Each suffix entry references a prefix entry, which is stored in the same index block as the suffix entry.

. The maximum prefix length for a nonunique index is the number of key columns, and the maximum prefix length for a unique index is the number of key columns minus one. Prefix entries are written to the index block only if the index block does not already contain a prefix entry whose value is equal to the present prefix entry. Prefix entries are available for sharing immediately after being written to the index block and remain available until the last deleted referencing suffix entry is cleaned out of the index block.

Performance and Storage Considerations
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. But the number of branch blocks, and the index height may not be reduced. Although key compression reduces the storage requirements of an index, it can increase the CPU time required to reconstruct the key column values during an index scan. It also incurs some additional storage overhead, because every prefix entry has an overhead of 4 bytes associated with it.

Uses of Key Compression
Key compression is useful in many different scenarios, such as:

• In a nonunique regular index, Oracle stores duplicate keys with the rowid appended to the key to break the duplicate rows. If key compression is used, Oracle stores the duplicate key as a prefix entry on the index block without the rowid. The rest of the rows are suffix entries that consist of only the rowid.
• This same behavior can be seen in a unique index that has a key of the form (item, time stamp), for example (stock_ticker, transaction_time). Thousands of rows can have the same stock_ticker value, with transaction_time preserving uniqueness. On a particular index block a stock_ticker value is stored only once as a prefix entry. Other entries on the index block are transaction_time values stored as suffix entries that reference the common stock_ticker prefix entry.
• In an index-organized table that contains a VARRAY or NESTED TABLE datatype, the object ID (OID) is repeated for each element of the collection datatype. Key compression lets you compress the repeating OID values.
• In some cases, however, key compression cannot be used. For example, in a unique index with a single attribute key, key compression is not possible, because even though there is a unique piece, there are no grouping pieces to share.

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.

Validating indexes for Compression
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):

The validate command in sql*plus can be used to estimate the benefits of compression. Consider the following example

SQL> create table my_objects as select object_name oname, object_type otype from all_objects;

Table created.

SQL> select count(*) from my_objects;

  COUNT(*)
----------
      5737

SQL> select count(distinct otype) from my_objects;

COUNT(DISTINCTOTYPE)
--------------------
                  18

SQL> create index my_obj_idx1 on my_objects(otype, oname);

Index created.

SQL> validate index my_obj_idx1;

Index analyzed.

SQL> select opt_cmpr_count, opt_cmpr_pctsave from  index_stats;

OPT_CMPR_COUNT OPT_CMPR_PCTSAVE
-------------- ----------------
             1               19

In the above example, using compression will use only use 19% of the space. So compression is recommended.