Advanced index compression (HIGH) - Part III

 

If you like the new advanced index compression setting of “High”, well there is a way to make this as a default index compression method in the database using a parameter introduced in 12.2 database.

 

Let’s begin by creating a table and explicitly creating a non-compressed index.

 

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.

 

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

 

demo@ORA12C>

 

We noticed that the index has around 2124 leaf blocks and that index compression is indeed disabled by default.

 

To make the index compression by default at this database, all we need to do is set this parameter “db_index_compression_inheritance” from which during the index creation the index inherits its index compression attributes (tablespace or table or not at all).

 

demo@ORA12C> create tablespace ts_index_comp

  2  datafile 'D:\APP\VNAMEIT\VIRTUAL\ORADATA\ORA12C\ts_index_comp.DBF'

  3  size 10M autoextend on

  4  maxsize 1G

  5  default index compress advanced high ;

 

Tablespace created.

 

demo@ORA12C> alter user demo quota unlimited on ts_index_comp;

 

User altered.

 

demo@ORA12C> show parameter db_index_compression

 

NAME                                 TYPE        VALUE

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

db_index_compression_inheritance     string      NONE

demo@ORA12C> alter session set db_index_compression_inheritance=tablespace;

 

Session altered.

 

demo@ORA12C> drop index t_idx ;

 

Index dropped.

 

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

  2  tablespace ts_index_comp

  3  nologging;

 

Index created.

 

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

 

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

 

demo@ORA12C>

 

The index has now only 777 leaf blocks, and that it has automatically used the new index advanced compression option of HIGH.

 

So in this database, all index build on this tablespace TS_INDEX_COMP are automatically created with advanced index compression set to HIGH, provided that the “db_index_compression_inheritance” parameter is set at session/instance/database level.

 

One has to be very clear that, creating an index on the tablespace configured for advanced index compression will work out (like the above demo), however moving index from one tablespace to another tablespace (which got advanced index compression enabled) will not work out.

 

demo@ORA12C> show parameter db_index_compression_inheritance

 

NAME                                 TYPE        VALUE

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

db_index_compression_inheritance     string      TABLESPACE

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

 

Index created.

 

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

 

demo@ORA12C> alter index t_idx rebuild tablespace ts_index_comp;

 

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      DISABLED             2124

 

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

 

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

 

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

 

demo@ORA12C>

 

So if you have an application migrating to 12c and would like to take the advantage of advanced index compression HIGH, then just setting this parameter “db_index_compression_inheritance” and rebuilding the index from one tablespace to another tablespace (which got advanced index compression enabled) will not work out.

 

Either it has to be dropped and recreated in the new tablespace (which got advanced index compression enabled) or it should be rebuilt with the option of “compress advanced”.