Advanced index compression (HIGH) - Part I

 

Oracle introduced the advanced index compression in Oracle 12c Release 1 and have discussed that before.

 

With Oracle 12c Release 2 we can now use advanced index compression High to further improve the index compression ratio. Instead of simply de-duplicating the index entries with in the leaf block, High index compression uses more complex compression algorithm and store the index entries in compression units (similar to the one used with HCC in Exadata platform). The net result is generally the much better level of compression, but at the potential cost of more CPU resource to both access and maintain the index structures.

 

To get an idea about advanced index compression High, let’s re-run the demo from the previous example.

 

demo@ORA12C> create table t

  2  as

  3  select owner,object_name,subobject_name,object_id,

  4     data_object_id,object_type,created,last_ddl_time,

  5     timestamp,status,temporary,generated,secondary,

  6     namespace,edition_name,sharing,editionable,

  7     oracle_maintained, case when id between

  8                     0 and 250000 then 0

  9                     else id end id

 10  from big_table;

 

Table created.

 

 

The column ID has 25% of duplicate values and 75% of unique values, we then create an index on this ID column and check the size.

 

 

demo@ORA12C> create index t_idx on t(id) nologging;

 

Index created.

 

demo@ORA12C> col index_name format a10

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      DISABLED             2124

 

 

Now we use the normal prefix compression on this index

 

 

demo@ORA12C> alter index t_idx rebuild compress nologging;

 

Index altered.

 

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ENABLED              2685

 

 

We noticed that the index size got increased with compression, due to the overheads associated with resultant prefix table.

 

If we use advanced index compression LOW introduced in 12.1 database

 

demo@ORA12C> alter index t_idx rebuild compress advanced LOW nologging;

 

Index altered.

 

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ADVANCED LOW         2054

 

 

We noticed that the index size got decreased, by effectively compressing 25% of data where compression is beneficial and not touched the 75% of index where compression wasn’t possible.

 

If we however use the new 12.2 advanced index compression HIGH option:

 

demo@ORA12C> alter index t_idx rebuild compress advanced HIGH nologging;

 

Index altered.

 

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ADVANCED HIGH           0

 

 

The leaf block is zero due the bug 22094934. We need to gather the index statistics to see the new index size.

 

demo@ORA12C> exec dbms_stats.gather_index_stats(user,'T_IDX');

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ADVANCED HIGH         777

 

 

The index is now substantially smaller than before. Without compression it was 2124 blocks and with advanced compression low it is 2054 block and with advanced compression high it is only 777 blocks.

 

Starting with 12c release 2, the default option for advanced index compression is HIGH.

 

demo@ORA12C> create table t as select * from all_objects;

 

Table created.

 

demo@ORA12C> create index t_idx on t( object_name )

  2  nologging

  3  compress

  4  advanced ;

 

Index created.

 

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where table_name ='T';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ADVANCED HIGH           0

 

demo@ORA12C> exec dbms_stats.gather_index_stats(user,'T_IDX');

 

PL/SQL procedure successfully completed.

 

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where table_name ='T';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

---------- ------------- -----------

T_IDX      ADVANCED HIGH         188

 

So Index Advanced Compression, with the now default “HIGH” option can substantially reduce index sizes. Note this new capability of course requires the Advanced Compression Option.