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

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

  COMPRESS ADVANCED LOW

Example

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

begin
  for i in 1..1000000
  loop
    insert into test_tbl 
    (
      id1, id2,
      id3, id4,
      id5, id6,
      id7, id8
    )   
    values
    (
      i,mod(i,8),
      mod(i,4), mod(i,8),
      i,mod(i,8),
      mod(i,4), mod(i,8)
    );
  end loop;
  
  commit;
end;
/

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
select 
  count(*),
  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:]]') ;

SEGMENT_NAME    SEGMENT_TYPE            BYTES
--------------- ------------------ ----------
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 🙂

Tomasz