Oracle Database can use prefix compression, also known as key compression, to compress portions of the primary key column values in a B-tree index or an index-organized table. Prefix compression can greatly reduce the space consumed by the index.
An uncompressed index entry has one piece. An index entry using prefix compression has two pieces: 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 the suffix entries in an index block.
Note:
If a key is not defined to have a unique piece, then the database provides one by appending a rowid to the grouping piece.
By default, the prefix of a unique index consists of all key columns excluding the last one, whereas the prefix of a nonunique index consists of all key columns. Suppose you create a composite, unique index on two columns of the oe.orders table as follows:
CREATE UNIQUE INDEX orders_mod_stat_ix ON orders ( order_mode, order_status );
In the preceding example, an index key might be online,0. The rowid is stored in the key data portion of the entry, and is not part of the key itself.
Note:
If you create a unique index on a single column, then Oracle Database cannot use prefix key compression because no common prefixes exist.
Alternatively, suppose you create a nonunique index on the same columns:
CREATE INDEX orders_mod_stat_ix ON orders ( order_mode, order_status );
Also assume that repeated values occur in the order_mode and order_status columns. An index block could have entries as shown in the follow example:
online,0,AAAPvCAAFAAAAFaAAaonline,0,AAAPvCAAFAAAAFaAAgonline,0,AAAPvCAAFAAAAFaAAlonline,2,AAAPvCAAFAAAAFaAAmonline,3,AAAPvCAAFAAAAFaAAqonline,3,AAAPvCAAFAAAAFaAAt
In the preceding example, the key prefix would consist of a concatenation of the order_mode and order_status values, as in online,0. The suffix consists in the rowid, as in AAAPvCAAFAAAAFaAAa. The rowid makes the whole index entry unique because a rowid is itself unique in the database.
If the index in the preceding example were created with default prefix compression (specified by the COMPRESS keyword), then duplicate key prefixes such as online,0 and online,3 would be compressed. Conceptually, the database achieves compression as follows:
online,0AAAPvCAAFAAAAFaAAaAAAPvCAAFAAAAFaAAgAAAPvCAAFAAAAFaAAlonline,2AAAPvCAAFAAAAFaAAmonline,3AAAPvCAAFAAAAFaAAqAAAPvCAAFAAAAFaAAt
Suffix entries (the rowids) 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.
Alternatively, you could specify a prefix length when creating an index that uses prefix compression. For example, if you specified COMPRESS 1, then the prefix would be order_mode and the suffix would be order_status,rowid. For the values in the index block example, the index would factor out duplicate occurrences of the prefix online, which can be represented conceptually as follows:
online0,AAAPvCAAFAAAAFaAAa0,AAAPvCAAFAAAAFaAAg0,AAAPvCAAFAAAAFaAAl2,AAAPvCAAFAAAAFaAAm3,AAAPvCAAFAAAAFaAAq3,AAAPvCAAFAAAAFaAAt
The index stores a specific prefix once per leaf block at most. 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.
See Also:
-
Oracle Database Administrator's Guide to learn how to use compressed indexes
-
Oracle Database VLDB and Partitioning Guide to learn how to use prefix compression for partitioned indexes
-
Oracle Database SQL Language Reference for descriptions of the
key_compressionclause ofCREATE INDEX
