8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23ai | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Index Key Compression

From Oracle 9i onward both non-unique and unique indexes can have their key elements compressed to save space. The COMPRESS [N] clause indicates compression is required, with "N" optionally specifying the prefix, or number of keys to compress. The default for index creation is NOCOMPRESS.

Remember, index key compression only achieves high levels of compression if there are a lot of repeated keys in the index, so results will vary depending on your data.

Setup
Non-Unique Indexes
Unique Indexes
Restrictions
11g Updates
Advanced Index Compression in 12c

Setup

The following table is used in the examples in this article. The first five columns contain the same data in each row, but the last column contains a unique value in each row.

DROP TABLE t1;

CREATE TABLE t1 (
  col_1 VARCHAR2(50),
  col_2 VARCHAR2(50),
  col_3 VARCHAR2(50),
  col_4 VARCHAR2(50),
  col_5 VARCHAR2(50),
  col_6 VARCHAR2(50)
);

INSERT /*+ APPEND */ INTO t1
SELECT RPAD('X',50, 'X'),
       RPAD('X',50, 'X'),
       RPAD('X',50, 'X'),
       RPAD('X',50, 'X'),
       RPAD('X',50, 'X'),
       RPAD(TO_CHAR(level),50, 'X')
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

Non-Unique Indexes

For non-unique indexes, the prefix length can be from 1 to the total number of index keys, with the default being the total number of keys.

Build an index on all 6 columns and check the size of the index.

DROP INDEX t1_idx;
CREATE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6);

EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

SELECT bytes
FROM   user_segments
WHERE  segment_name = 'T1_IDX';

     BYTES
----------
   4194304

SQL>

We know there is a lot of repetitive data in the first five columns, so recreate the index compressing the first five keys, then check the index size.

DROP INDEX t1_idx;
CREATE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6) COMPRESS 5;

EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

SELECT bytes
FROM   user_segments
WHERE  segment_name = 'T1_IDX';

     BYTES
----------
    851968

SQL>

We can see there is a substantial space saving.

Unique Indexes

For unique indexes, the prefix length can be from 1 to the total number of index keys minus 1, with the default being the total number of keys minus 1. As a result, index key compression is only available for concatenated unique indexes.

DROP INDEX t1_idx;
CREATE UNIQUE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6);

EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

SELECT bytes
FROM   user_segments
WHERE  segment_name = 'T1_IDX';

     BYTES
----------
   4194304

SQL>

We know there is a lot of repetitive data in the first five columns, so recreate the unique index compressing the first five keys, then check the index size.

DROP INDEX t1_idx;
CREATE UNIQUE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6) COMPRESS 5;

EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

SELECT bytes
FROM   user_segments
WHERE  segment_name = 'T1_IDX';

     BYTES
----------
    851968

SQL>

Once again, we can see there is a substantial space saving.

Restrictions

The following restrictions apply to index key compression.

Bitmap indexes can't be compressed.
Partitioned indexes can't be compressed. This restrictions was lifted in Oracle 11g.

11g Updates

Oracle 11g lifted the restriction on using index key compression with partitioned indexes.

If you require a mix of compressed and non-compressed index partitions, you should create the index as a compressed index, then disable index key compression at the partition level when required.

Advanced Index Compression in 12c

Advanced Index Compression is part of Oracle Advanced Compression, which is an Oracle Enterprise Edition option.

Oracle 12c Release 1 (12.1) introduced advanced key compression using the COMPRESS ADVANCED LOW clause.

DROP INDEX t1_idx;
CREATE UNIQUE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6) COMPRESS ADVANCED LOW;

EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

SELECT bytes
FROM   user_segments
WHERE  segment_name = 'T1_IDX';

     BYTES
----------
    851968

SQL>

Oracle 12c Release 2 (12.2) introduced advanced key compression using the COMPRESS ADVANCED HIGH clause.

DROP INDEX t1_idx;
CREATE UNIQUE INDEX t1_idx ON t1(col_1, col_2, col_3, col_4, col_5, col_6) COMPRESS ADVANCED HIGH;

EXEC DBMS_STATS.gather_index_stats(USER, 't1_idx');

SELECT bytes
FROM   user_segments
WHERE  segment_name = 'T1_IDX';

     BYTES
----------
    196608

SQL>

For more information see:

Hope this helps. Regards Tim...

Back to the Top.