How to compress index subpartitions

One of many capabilities of Oracle database is the ability to compress the storage segments, However this compression is little bit tricky when it comes to compressing the Index subpartitions.

 

Lets start explaining with a small example.

 

demo@QES1> create table t

  2  partition by range( created )

  3  subpartition by hash( object_id )

  4  subpartition template( subpartition sp1,subpartition sp2)

  5  ( partition p_2018 values less than

  6         (to_date('01-jan-2019','dd-mon-yyyy')) ,

  7    partition p_2019 values less than

  8         (to_date('01-jan-2020','dd-mon-yyyy')) ,

  9    partition p_2020 values less than

 10         (to_date('01-jan-2021','dd-mon-yyyy')) ,

 11    partition p_2021 values less than

 12         (to_date('01-jan-2022','dd-mon-yyyy')) )

 13  as

 14  select a.*

 15  from all_objects a,

 16   all_users

 17  where rownum <=1000000;

 

Table created.

 

demo@QES1> create index t_idx on t( owner,object_type,object_id ) local;

Index created.

Got a composite partitioned table – by range-Hash – along with a local index, and the size of the leaf block for each index subpartitions were given below.

 

demo@QES1> select partition_name,subpartition_name,compression,leaf_blocks

  2  from user_ind_subpartitions

  3  where index_name ='T_IDX';

 

PARTITION_ SUBPARTITI COMPRESSION   LEAF_BLOCKS

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

P_2018     P_2018_SP1 DISABLED                0

P_2018     P_2018_SP2 DISABLED                0

P_2019     P_2019_SP1 DISABLED             1978

P_2019     P_2019_SP2 DISABLED             1937

P_2020     P_2020_SP1 DISABLED                3

P_2020     P_2020_SP2 DISABLED                4

P_2021     P_2021_SP1 DISABLED                0

P_2021     P_2021_SP2 DISABLED                0

 

8 rows selected.

 

Based on the wire diagram from docs, it shows that we can still compress the index (sub)partitions,

 

However if we try to compress the individual index subpartition or a partition, we will end up with an error like this

 

demo@QES1> alter index t_idx rebuild subpartition P_2019_SP1 compress advanced low;

alter index t_idx rebuild subpartition P_2019_SP1 compress advanced low

                                                  *

ERROR at line 1:

ORA-14189: this physical attribute may not be specified for an index subpartition

 

 

demo@QES1> alter index t_idx rebuild partition P_2019 compress advanced low;

alter index t_idx rebuild partition P_2019 compress advanced low

            *

ERROR at line 1:

ORA-14287: cannot REBUILD a partition of a composite partitioned index

 

So to compress the subpartitions effectively, we need to define the compression attribute at partition level, something like this

 

demo@QES1> alter index t_idx modify partition P_2019 compress advanced low;

Index altered

 

However just defining the compression, don’t reduce the storage foot prints, the size of the leaf blocks were still the same, before compression is getting applied.

 

demo@QES1> select partition_name,subpartition_name,compression,leaf_blocks

  2  from user_ind_subpartitions

  3  where index_name ='T_IDX';

 

PARTITION_ SUBPARTITI COMPRESSION   LEAF_BLOCKS

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

P_2018     P_2018_SP1 DISABLED                0

P_2018     P_2018_SP2 DISABLED                0

P_2019     P_2019_SP1 ADVANCED LOW         1978

P_2019     P_2019_SP2 ADVANCED LOW         1937

P_2020     P_2020_SP1 DISABLED                3

P_2020     P_2020_SP2 DISABLED                4

P_2021     P_2021_SP1 DISABLED                0

P_2021     P_2021_SP2 DISABLED                0

 

8 rows selected.

 

To get the compression’s effect in place, we need to rebuild the subpartiton index segments.

 

demo@QES1> alter index t_idx rebuild subpartition P_2019_SP1;

 

Index altered.

 

demo@QES1> select partition_name,subpartition_name,compression,leaf_blocks

  2  from user_ind_subpartitions

  3  where index_name ='T_IDX';

 

PARTITION_ SUBPARTITI COMPRESSION   LEAF_BLOCKS

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

P_2018     P_2018_SP1 DISABLED                0

P_2018     P_2018_SP2 DISABLED                0

P_2019     P_2019_SP1 ADVANCED LOW          816

P_2019     P_2019_SP2 ADVANCED LOW         1937

P_2020     P_2020_SP1 DISABLED                3

P_2020     P_2020_SP2 DISABLED                4

P_2021     P_2021_SP1 DISABLED                0

P_2021     P_2021_SP2 DISABLED                0

 

8 rows selected.

 

demo@QES1>