Oracle 12C - Creating an Index Using Advanced Index Compression

This feature is available starting with Oracle Database 12c Release 1 (12.1.0.2).

Creating an index using advanced index compression reduces the size of all supported unique and non-unique indexes. Advanced index compression improves the compression ratios significantly while still providing efficient access to the indexes. Therefore, advanced index compression works well on all supported indexes, including those indexes that are not good candidates for prefix compression.

For a partitioned index, you can specify the compression type on a partition by partition basis. You can also specify advanced index compression on index partitions even when the parent index is not compressed.

Advanced index compression works at the block level to provide the best compression for each block. When a CREATE INDEX DDL statement is executed, a block is filled with rows. When it is full, it is compressed with advanced index compression if enough space is saved to insert the next row. When DML statements or other types of DDL statements are executed, and a block becomes full and is about to be split, the block might be recompressed using advanced index compression to avoid the split if enough space is saved to insert the incoming key.

Before enabling advanced index compression, the database must be at 12.1.0 or higher compatibility level. You enable advanced index compression using the COMPRESS ADVANCED LOW clause. For example, the following statement enables advanced index compression during the creation of the hr.emp_mndp_ix index:

CREATE INDEX hr.emp_mndp_ix ON hr.employees(manager_id, department_id)
   COMPRESS ADVANCED LOW;

You can also specify the COMPRESS ADVANCED LOW clause during an index rebuild. For example, during rebuild, you can enable advanced index compression for the hr.emp_manager_ix index as follows:

ALTER INDEX hr.emp_manager_ix REBUILD COMPRESS ADVANCED LOW;
The COMPRESSION column in the ALL_INDEXES, ALL_IND_PARTITIONS, and ALL_IND_SUBPARTITIONS views shows whether an index is compressed, and, if it is compressed, the type of compression enabled for the index.

The following example shows a mixture of compression attributes on the partition indexes.

CREATE INDEX my_test_idx ON test(a, b) COMPRESS ADVANCED HIGH LOCAL
   (PARTITION p1 COMPRESS ADVANCED LOW,
    PARTITION p2 COMPRESS,
    PARTITION p3,
    PARTITION p4 NOCOMPRESS);

The following example shows advanced index compression support on partitions where the parent index is not compressed.

CREATE INDEX my_test_idx ON test(a, b) NOCOMPRESS LOCAL
   (PARTITION p1 COMPRESS ADVANCED LOW,
    PARTITION p2 COMPRESS ADVANCED HIGH,
    PARTITION p3);