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.
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.
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
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.
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.
Posted on January 23, 2008, in Datawarehousing. Bookmark the permalink. 7 Comments.
Thanks for the tip. — This help me 🙂
Very nice !! That is what I was looking for !
That is well solution
This is a great work-around. Many Thanks!
Amazing. Thanks so much for this work-around.
Just what I wanted…I knew it could be done, but not how…thanks.
In the 11R2 it is allowed to set compression om a subpartition level:
partition by range(last_letters)
subpartition by range(DTMODIFIKATION)
subPARTITION part_m1 VALUES LESS THAN (TO_DATE(’01-02-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m2 VALUES LESS THAN (TO_DATE(’01-03-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m3 VALUES LESS THAN (TO_DATE(’01-04-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m4 VALUES LESS THAN (TO_DATE(’01-05-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m5 VALUES LESS THAN (TO_DATE(’01-06-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m6 VALUES LESS THAN (TO_DATE(’01-07-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m7 VALUES LESS THAN (TO_DATE(’01-08-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m8 VALUES LESS THAN (TO_DATE(’01-09-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m9 VALUES LESS THAN (TO_DATE(’01-10-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m10 VALUES LESS THAN (TO_DATE(’01-11-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m11 VALUES LESS THAN (TO_DATE(’01-12-2011′,’DD-MM-YYYY’)) compress,
subPARTITION part_m12 VALUES LESS THAN (TO_DATE(’01-01-2012′,’DD-MM-YYYY’)),
subPARTITION future VALUES LESS THAN (MAXVALUE)