Compress some or all partitions of a B-tree index using key compression

Discussion

• Nirja Shah   -Posted on 28 Sep 15
  - You can compress some or all partitions of a B-tree index using key compression.
  
  - Key compression is applicable only to B-tree indexes.
  
  - Bitmap indexes are stored in a compressed manner by default.
  
  - An index using key compression eliminates repeated occurrences of key column prefix values, thus saving space and I/O.
  
  - The following example creates a local partitioned index with all partitions except the most recent one compressed:
  
  CREATE INDEX i_cost1 ON costs_demo (prod_id) COMPRESS LOCAL
  (PARTITION costs_old, PARTITION costs_q1_2003,
  PARTITION costs_q2_2003, PARTITION costs_recent NOCOMPRESS);
  
  - You cannot specify COMPRESS (or NOCOMPRESS) explicitly for an index subpartition.
  
  - All index subpartitions of a given partition inherit the key compression setting from the parent partition.
  
  - To modify the key compression attribute for all subpartitions of a given partition, you must first issue an ALTER INDEX...MODIFY PARTITION statement and then rebuild all subpartitions.
  
  - The MODIFY PARTITION clause marks all index subpartitions as UNUSABLE.

➨ Post your comment / Share knowledge