Oracle9i New Feature Series: Index Key Compression
Index key compression introduced in Oracle9i lets you compress the leading columns of an index (or index organized table) to save space. Oracle compresses only non-partitioned indexes that are non-unique or unique indexes of at least two columns. Bitmap indexes cannot be compressed.
Generally, keys in an index have two pieces, a grouping piece and a unique piece. If the key is not defined to have a unique piece, Oracle provides one in the form of a rowid appended to the grouping piece. Key compression is a method of breaking off the grouping piece and storing it so it can be shared by multiple unique pieces.
Key compression is achieved by breaking the index entry into two pieces – a prefix entry (or the grouping piece) and the suffix entry (the unique piece). 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.
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.
Example creating a compressed index-organized table:
Example creating a compressed index:
You can specify an integer along with the COMPRESS clause, which specifies the number of prefix columns to compress. For unique indexes, the valid range of prefix length values is from 1 to the number of key columns minus 1. The default is the number of key columns minus 1. For non-unique indexes, the valid range is from 1 to the number of key columns. The default is the number of key columns.