As we discussed in the Part 1 of this blog series, index entries with many duplicate keys (or duplicate prefix columns in the index keys) can be compressed making it possible to reduce both the storage overhead and the access overhead for large index range scans or fast full scans.

CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS;

Index Key Compression (also known as prefix compression) eliminates duplicate copies of pre-defined number of index prefix columns at the index leaf block level and is an effective way to permanently reduce the index size, both on disk and in cache.

The number of prefix columns to consider for compression is specified by the DBA at the index create time (or rebuild time) and is constant for all index leaf blocks. Compression can be very beneficial when the prefix columns of an index have many repeated rows within a leaf block.

However, when the leading columns are very selective, or if there are not many repeat for the prefix columns, it is possible to make indexes larger than their uncompressed equivalent due to the overhead to store the prefix entries. Thus, it is very critical to compress the right indexes and with correct number of prefix columns.

This approach for Index Key Compression has the following down sides:

  • Requires the DBA to have a deep understanding of their data in order to choose the most optimal prefix column count
  • Specified prefix column count may not be optimal to produce the best compression ratio for every block in the index
  • Requires running ANALYZE INDEX to obtain the optimal prefix column count, which produces the optimal count for the index as a whole. This is not at the granularity of a block, so may not yield the best compression ratio. Additionally, running ANALYZE INDEX takes an exclusive lock on the table, effectively making the table “offline” for this period
  • Possible to get negative compression, as pointed out earlier, such as in the case where the specified prefix columns are unique in a block

You need to be very selective on which indexes to compress and correctly set the prefix column count for these indexes. Oracle protects you under certain obvious conditions, but it is your responsibility, as a DBA, to compress the indexes in the right manner.

So, why doesn’t Oracle automatically decide which indexes to compress and automatically computed the prefix column count within compressed indexes? Additionally, why does Oracle use the static prefix count approach in which all the leaf blocks are compressed with the same prefix count?

If you have been struggling with such questions, the answer is in Advanced Index Compression.

Advanced Index Compression is part of Advanced Compression Option in 12.1.0.2 and aims to automate index compression so that a DBA is no longer required to specify the number of prefix columns to consider for compression.

The correct and most optimal numbers of prefix columns will be computed automatically on block by block bases, and thus produce the best compression ratio possible. It is now possible to have different index leaf blocks compressed differently (with different prefix column count) or not be compressed at all, if there are no repeating prefixes. The illustration below shows logical structure of three consecutive index leaf blocks, each compressed differently.

Advanced Index Compression can be enabled easily by specifying the COMPRESS option for indexes. New indexes can be automatically created as compressed, or the existing indexes can be rebuilt compressed.

CREATE INDEX idxname ON tabname(col1, col2, col3) COMPRESS ADVANCED LOW;

Note that there is no need to provide the number of columns in the prefix entries with Advanced Index Compression as this will be computed automatically for every leaf block.

Advanced Index Compression works well on all supported indexes, including the ones that were not good candidates for prefix key compression. Creating an index using Advanced Index Compression reduces the size of all unique and non-unique indexes (or at least doesn’t increase the size due to negative compression) and at the same time improves the compression ratio significantly while still providing efficient access to the indexes.

For partitioned indexes, you can specify the compression clause for the entire index or on a partition-by-partition basis. So you can choose to have some index partitions compressed, while others are not.

The following example shows a mixture of compression attributes on the partitioned indexes:

CREATE INDEX my_test_idx ON test(a, b) COMPRESS ADVANCED LOW 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 LOW,
  PARTITION p3);

The current release of Advanced Index Compression has following limitations:

  • Advanced Index Compression is not supported on Bitmap Indexes
  • You cannot compress your Index Organized Tables (IOTs) with Advanced Index Compression
  • You cannot compress your Functional Indexes with Advanced Index Compression 

With this feature for indexes, it is now possible to simply enable compression for all your B-Tree indexes, and Oracle will automatically compress every index leaf block when beneficial, while taking care of computing the optimal prefix column length for every block. This makes index compression truly local at a block level, where both the compression prefix table as well as the decision on how to compress the leaf block is made locally for every block and aims towards achieving the most optimal compression ratio for the entire index segment.

A very informative video by Dominic Giles, demonstrating the ease of use and performance characteristics of Advanced Index Compression, is available here.