Advanced index compression (HIGH) - Part V

 

Have discussed enough in the past how advanced index compression can automatically determine not only the correct number of columns to compress, but also the correct number of columns to compress within the specific leaf block of the index.

 

Also discussed how ordering of column within the index can be very important including with regard to the possible compression capability of an index.

 

Ordering of the columns in the index is still an important criteria for Key compressed b*Tree index, However with Advanced index compression (HIGH) this ordering is not an potential criteria( that is even with the columns having high distinct values in the leading portion of the index can be compressed very effectively using Advanced index compression techniques)

 

To illustrate, here is a simple example.

 

demo@PDB1> create table t as

  2  select *

  3  from big_table;

 

Table created.

 

The table T has around million rows, but now going to build an index on the effectively unique ID column in the leading position, followed by other columns that has many duplicate values.

 

demo@PDB1> select c.column_name, t.num_rows,c.num_distinct,c.num_nulls

  2  from user_tables t,

  3      user_tab_col_statistics c

  4  where t.table_name = c.table_name

  5  and t.table_name ='T'

  6  and c.column_name in ('ID','OWNER') ;

 

COLUMN_NAME            NUM_ROWS NUM_DISTINCT  NUM_NULLS

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

OWNER                   1000000           17          0

ID                      1000000      1000000          0

 

demo@PDB1> create index t_idx on t(id,owner) nologging;

 

Index created.

 

demo@PDB1> col index_name format a10

demo@PDB1> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

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

T_IDX      DISABLED             2905

 

The index without compression has 2905 leaf blocks.

 

As the leading column is effectively unique, we simply can’t now compress this index effectively. That’s because the compression requires there to be duplicate index entries. If the leading column has few or no duplicate values, then by compressing the index oracle is effectively creating a prefixed entry with in the leaf block for each and every index entry. The whole point of index compression is to effectively de-duplicate index values, but there’s nothing to de-duplicate if there are no repeating values in the leading column of the index.

 

If we just attempt to compress fully the index, then

 

demo@PDB1> alter index t_idx rebuild compress nologging;

 

Index altered.

 

demo@PDB1> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

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

T_IDX      ENABLED              3745

 

Results in a bigger not a smaller index. The number of leaf blocks gone up from 2905 to 3745.

If we attempt to compress the first column then it makes no difference to the inefficiency of index compression. Since with compress 1 the number of entries in the prefixed table remains the same.

 

demo@PDB1> alter index t_idx rebuild compress 1 nologging;

 

Index altered.

 

demo@PDB1> select index_name,compression,leaf_blocks

  2  from user_indexes

  3  where index_name ='T_IDX';

 

INDEX_NAME COMPRESSION   LEAF_BLOCKS

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

T_IDX      ENABLED              3745

 

The good thing with advanced index compression is that we can give it a go, but it will “not” result in a bigger index structure.  If there is nothing to compress within the leaf block, oracle just ignores it and moves to the next leaf block. If there is nothing to compress within the index, then index remains the same as if it is not been compressed.

 

demo@PDB1> alter index t_idx rebuild compress advanced low;

 

Index altered.

 

demo@PDB1> 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         2905

 

The index is now back to 2905 leaf blocks, the same way as it is not compressed at all. However with advanced index compression HIGH it is still possible to compress the index effectively and order of the column is no significant.

 

demo@PDB1> alter index t_idx rebuild compress advanced high;

 

Index altered.

 

demo@PDB1> 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

 

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

 

PL/SQL procedure successfully completed.

 

demo@PDB1> 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        1006

 

demo@PDB1>

 

We now have an index structure at just 1006 leaf blocks that is indeed smaller than the uncompressed index (2905 leaf blocks).

 

The best of both worlds, where Index Advanced Compression can compress just the part of an index where it effectively can and ignore and not make matters worse in any parts of the index where index compression is ineffective.