Advanced Index Compression Oracle Database 12C release 1 (12.1)

Advanced Index Compression is available starting with Oracle Database 12c Release 1 (

It improves compression ratio for multi column indexes that in previous releases were not good candidates for prefix compression. It reduces the size of all supported unique and non-unique indexes. It still providing efficient access to the indexes.

Notes about advanced index compression

  • it is not supported for bitmap indexes or index-organized tables.

  • it cannot be specified on a single column unique index.

  • it can be specified for whole index or just on index partition level
  • extended clause COMPRESS ADVANCED LOW


I have created simple table TEST_TBL to test Advanced Index Compression

create table test_tbl
  id1 number,
  id2 number,
  id3 number,
  id4 number,
  id5 number,
  id6 number,
  id7 number,
  id8 number

generated dummy data and created four indexes:

  • c_idx1 – not well candidate for prefix compression with old COMPRESS
  • c_idx2 – well candidate for prefix compression with old COMPRESS
  • cal_adv_idx3 – not well candidate for prefix compression with old COMPRESS but should be good candidate with new COMPRESS ADVANCED LOW
  • cal_idx4 – well candidate for prefix compression with old COMPRESS and new COMPRESS ADVANCED LOW
  for i in 1..1000000
    insert into test_tbl 
      id1, id2,
      id3, id4,
      id5, id6,
      id7, id8
      mod(i,4), mod(i,8),
      mod(i,4), mod(i,8)
  end loop;

create index c_idx1 on test_tbl(id1, id2) compress;
create index c_idx2 on test_tbl(id3, id4) compress;
create index cal_idx3 on test_tbl(id5, id6) compress advanced low;
create index cal_idx4 on test_tbl(id7, id8) compress advanced low;

following query shows number of distinct columns for prefix column in each index. Index C_IDX1 is generated on the same data as CAL_IDX3 and C_IDX2 as CAL_IDX4.

--distinct count for prefix columns for the indexes
  count(distinct id1) c_idx1,
  count(distinct id3) c_idx2,
  count(distinct id5) cal_idx3,
  count(distinct id7) cal_idx4
from test_tbl;

  COUNT(*)     C_IDX1     C_IDX2   CAL_IDX3   CAL_IDX4
---------- ---------- ---------- ---------- ----------
   1000000    1000000          4    1000000          4

With Advanced Index Compression space consumed by segments shows that COMPRESS ADVANCED LOW helps to save a lot of space for index CAL_IDX3 comparing to C_IDX2. There are no difference between index CAL_IDX4 and C_IDX1.

select segment_name, segment_type, bytes
from user_segments
where segment_name in ('C_IDX1', 'C_IDX2', 'CAL_IDX3', 'CAL_IDX4')
order by regexp_replace(segment_name, '[^[:digit:]]') ;

--------------- ------------------ ----------
C_IDX1          INDEX                29360128 
C_IDX2          INDEX                13631488 
CAL_IDX3        INDEX                22020096 
CAL_IDX4        INDEX                13631488

Oracle 12C help for Advanced Index Compression

Have a fun 🙂


One thought on “Advanced Index Compression Oracle Database 12C release 1 (12.1)

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.