Advanced index compression (HIGH) - Part II

 

In that part1, we introduced the new advanced index compression default value of HIGH, which has the potential to significantly compress indexes much more than previously possible, that is due to new index compression algorithm that do more than simply de-duplicate values with in the leaf blocks.

 

Previously any attempt to compress all the key columns of a unique index is complete failure, since unique index by definition only has unique values and nothing to de-duplicate. But we are also previously restricted to compress only n-1 columns within the unique index. An attempt to compress all the column in a unique index would results in a larger index structure due to the associated overheads of the prefix-table within the leaf blocks.

 

Let’s first create a simple table with unique index.

 

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

 

Table created.

 

demo@ORA12C> create unique index t_idx on t( object_id );

 

Index created.

 

demo@ORA12C> col index_name format a10

demo@ORA12C> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where table_name ='T';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

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

T_IDX      DISABLED              150

 

demo@ORA12C>

 

So the uncompressed unique index has 150 blocks, if we try to use normal index compression (aka prefixed compression) on this index

 

demo@ORA12C> alter index t_idx rebuild COMPRESS;

alter index t_idx rebuild COMPRESS

*

ERROR at line 1:

ORA-25193: cannot use COMPRESS option for a single column key

 

We get an error saying, we are not allowed to compress a single column unique index, doing so makes no-benefit, since no duplicates were observed in that index for compression.

 

If we attempt to advance index compress with the value of LOW then

 

demo@ORA12C> alter index t_idx rebuild COMPRESS ADVANCED LOW;

alter index t_idx rebuild COMPRESS ADVANCED LOW

*

ERROR at line 1:

ORA-25193: cannot use COMPRESS option for a single column key

 

The same error. Although advanced index compression low is clever enough to automatically compress only those leaf block where there is a benefit in compression, there can be no such leaf block in this unique index that benefit from compression via the de-duplication method.

 

However if we use the new advance index compress with the value of HIGH then

 

demo@ORA12C> alter index t_idx rebuild COMPRESS ADVANCED HIGH;

 

Index altered.

 

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          71

 

demo@ORA12C>

 

Not only does it not give us an error, but it has actually managed to successfully compress such a unique index containing nothing but a bunch of unique numbers to just 71 leaf blocks, down from 150 leaf blocks. The index is now less than half its pervious size.

 

So any B*Tree index, even it is a single column unique index, is a potential candidate for compression with “HIGH” in advanced index compression.