Compressing Subpartition Segments

As far as I have recently read, Oracle doesn’t allow you to set some physical features of sub-partitions such as compression clause. I have been mistaken that composite partitioned tables can not be compressed at all. However in data warehouse environments, a general requirement is being able to compress segments of historic partitions of RANGE + HASH partitioned tables. I have tried a few scenarios and finally found a solution. Yes, Oracle allows it.
Naïve Approaches

One approach can be to compress all subsegments by trying recreating partition with COMPRESS clause in parallel. However this approach will not work:

ALTER TABLE X MOVE PARTITION 2007JAN NOLOGGING COMPRESS PARALLEL 16;

You will immediately face with ORA-14257: cannot move partition other than a Range or Hash partition exception. This means Oracle can not recreate all subpartition segments at a time.

Another approach is to try recreating compressed subpartition segments by using COMPRESS option one by one:

ALTER TABLE X MOVE SUBPARTITION 2007JAN_01 NOLOGGING COMPRESS PARALLEL 16;

This time you will come up with ORA-14160: this physical attribute may not be specified for a table subpartition. That’s because Oracle doesn’t allow you to set some physical attributes of subpartitions. NOLOGGING and COMPRESS are two of those attributes. Those attributes inherit from partition of subpartition.
Solution
Change the physical attribute at partition level

The first thing you have to do is to change physical attribute (compression attribute) of the partition which owns the subpartition by using MODIFY clause:

ALTER TABLE X MODIFY PARTITION 2007JAN COMPRESS;

This statement will modify partition parameter in such a way that all new data blocks created by direct path inserts will be compressed. Remember that the table has composite partitioning. This means subpartition segment blocks are the compressed blocks.
Recreate subpartition segments

“ALTER TABLE…” statement that we have used above affects only the blocks that are created after the statement has been issued. In order to compress existing subpartition blocks, we need to recreate them:

ALTER TABLE X MOVE SUBPARTITION 2007JAN_01 PARALLEL 16;

ALTER TABLE X MOVE SUBPARTITION 2007JAN_02 PARALLEL 16;

ALTER TABLE X MOVE SUBPARTITION 2007JAN_03 PARALLEL 16;

ALTER TABLE X MOVE SUBPARTITION 2007JAN_04 PARALLEL 16;

All these four subpartition segments will be recreated in compressed form within the tablespace(s) in which they have been currently residing. That is because they inherit the compression attribute from their parents (partition of subpartitions).
Check *_SEGMENTS view to see the effect

As you execute the following command you will see that your segments have already been compressed:

SELECT partition_name, bytes / 1024 / 1024 / 1024 gigabytes

FROM user_segments

WHERE segment_name = ‘X’;

In order to test this scenario I have used one of the summary tables of our data warehouse. Table is RANGE+HASH partitioned and one of these partitions is ~85 GB before compression and ~22 GB after all sixteen subpartitions have been compressed.

Conclusion

Remember that Oracle unfolds, considering that the term is correct, the compressed blocks if you update the data in compressed ones, which decreases the benefit you obtain by using compression. So Oracle advises using compression option with read only tablespaces to guarantee the gain.