Index KeyCompression

Oracle Database can use key compression to compressportions of the primary key column values in a B-treeindex or an index-organized table. Key compression can greatly reduce the spaceconsumed by the index.

In general, index keys have two pieces, a groupingpiece and a unique piece. Key compression breaks the index key into a prefix entry, which is the grouping piece,and a suffix entry, which is the unique or nearly unique piece.The database achieves compression by sharing the prefix entries among thesuffix entries in an index block.

Note:

If a key is not defined to have aunique piece, then the database provides one by appending a rowid to thegrouping piece.

By default, the prefix of a unique index consists of allkey columns excluding the last one, whereas the prefix of a nonunique index consistsof all key columns. For example, suppose that you create a composite index onthe oe.orders table as follows:

CREATE INDEX orders_mod_stat_ix ONorders ( order_mode, order_status );

Many repeated values occur in the order_mode and order_status columns. Anindex block may have entries as shown in Example 3-3.

Example 3-3Index Entries in Orders Table

online,0,AAAPvCAAFAAAAFaAAa

online,0,AAAPvCAAFAAAAFaAAg

online,0,AAAPvCAAFAAAAFaAAl

online,2,AAAPvCAAFAAAAFaAAm

online,3,AAAPvCAAFAAAAFaAAq

online,3,AAAPvCAAFAAAAFaAAt

In Example 3-3, the keyprefix would consist of a concatenation of the order_mode and order_status values. Ifthis index were created with default key compression, then duplicate keyprefixes such as online,0 and online,2 would becompressed. Conceptually, the database achieves compression as shown in thefollowing example:

online,0

AAAPvCAAFAAAAFaAAa

AAAPvCAAFAAAAFaAAg

AAAPvCAAFAAAAFaAAl

online,2

AAAPvCAAFAAAAFaAAm

online,3

AAAPvCAAFAAAAFaAAq

AAAPvCAAFAAAAFaAAt

Suffix entries form the compressed version of index rows.Each suffix entry references a prefix entry, which is stored in the same indexblock as the suffix entry.

Alternatively, you could specify a prefix length whencreating a compressed index. For example, if you specifiedprefix length 1, then the prefix would be order_mode and thesuffix would be order_status,rowid. For the values in Example 3-3, the indexwould factor out duplicate occurrences of online as follows:

online

0,AAAPvCAAFAAAAFaAAa

0,AAAPvCAAFAAAAFaAAg

0,AAAPvCAAFAAAAFaAAl

2,AAAPvCAAFAAAAFaAAm

3,AAAPvCAAFAAAAFaAAq

3,AAAPvCAAFAAAAFaAAt

The index stores a specific prefix once per leaf block atmost. Only keys in the leaf blocks of a B-tree index are compressed. In thebranch blocks the key suffix can be truncated, but the key is not compressed.

See Also:

· Oracle Database Administrator's Guide to learn howto use compressed indexes

· Oracle Database VLDB and PartitioningGuide to learn how to use key compression for partitionedindexes

· Oracle Database SQL Language Reference fordescriptions of the key_compression clause of CREATE INDEX

key_compressionSpecify COMPRESS to enable keycompression, which eliminates repeated occurrence of key column values and maysubstantially reduce storage. Use integer to specify the prefix length (numberof prefix columns to compress).

Oracle Database compresses indexes that are nonunique orunique indexes of at least two columns. If you want to use compression for apartitioned index, then you must create the index with compression enabled atthe index level. You can subsequently enable and disable the compressionsetting for individual partitions of such a partitioned index. You can alsoenable and disable compression when rebuilding individual partitions. You canmodify an existing non-partitioned index to enable or disable compression onlywhen rebuilding the index.

·For unique indexes, the valid range of prefix lengthvalues is from 1 to the number of key columns minus 1. The default prefix length is the number of key columns minus 1.

·For nonunique indexes, the valid range of prefix lengthvalues is from 1 to the number of key columns. The default prefix length is the number of key columns.

Restriction on Key CompressionYou cannotspecify COMPRESS for a bitmap index.

NOCOMPRESS Specify NOCOMPRESS to disablekey compression. This is the default.

Compressing anIndex: ExampleTo create the ord_customer_ix_demo index withthe COMPRESS clause, you might issue the following statement:

CREATE INDEX ord_customer_ix_demo

ON orders (customer_id, sales_rep_id)

COMPRESS 1;