Creating an index using key compression - Oracle Indexes and Partitioned Tables

Discussion

• Nirja Shah   -Posted on 28 Sep 15
  - Creating an index using key compression enables you to eliminate repeated occurrences of key column prefix values.
  
  - Key compression breaks an index key into a prefix and a suffix entry.
  
  - Compression is achieved by sharing the prefix entries among all the suffix entries in an index block.
  
  - This sharing can lead to huge savings in space, allowing you to store more keys for each index block while improving performance.
  
  - Key compression can be useful in the following situations:
  - You have a non-unique index where ROWID is appended to make the key unique. If you use key compression here, the duplicate key is stored as a prefix entry on the index block without the ROWID. The remaining rows become suffix entries consisting of only the ROWID.
  - You have a unique multicolumn index.
  
  - You enable key compression using the COMPRESS clause.
  
  - The prefix length (as the number of key columns) can also be specified to identify how the key columns are broken into a prefix and suffix entry.

➨ Post your comment / Share knowledge