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>
