Partitioning and Table Compression

You can compress several partitions or a complete partitioned heap-organized table.
You do this by either defining a complete partitioned table as being compressed, or by defining it on a per-partition level. Partitions without a specific declaration inherit the attribute from the table definition or, if nothing is specified on table level, from the tablespace definition.

To decide whether or not a partition should be compressed or stay uncompressed
adheres to the same rules as a nonpartitioned table. However, due to the capability of range and composite partitioning to separate data logically into distinct partitions, such a partitioned table is an ideal candidate for compressing parts of the data (partitions) that are mainly read-only. It is, for example, beneficial in all rolling window operations as a kind of intermediate stage before aging out old data. With data segment compression, you can keep more old data online, minimizing the burden of additional storage consumption.

You can also change any existing uncompressed table partition later on, add new
compressed and uncompressed partitions, or change the compression attribute as part
of any partition maintenance operation that requires data movement, such as MERGE
PARTITION, SPLIT PARTITION, or MOVE PARTITION. The partitions can contain
data or can be empty. The access and maintenance of a partially or fully compressed partitioned table are the same as for a fully uncompressed partitioned table. Everything that applies to fully uncompressed partitioned tables is also valid for partially or fully compressed partitioned tables.


Table Compression and Bitmap Indexes
If you want to use table compression on partitioned tables with bitmap indexes, you
need to do the following before you introduce the compression attribute for the first
time:
1. Mark bitmap indexes unusable.
2. Set the compression attribute.
3. Rebuild the indexes.
The first time you make a compressed partition part of an already existing, fully
uncompressed partitioned table, you must either drop all existing bitmap indexes or
mark them UNUSABLE prior to adding a compressed partition. This must be done
irrespective of whether any partition contains any data. It is also independent of the operation that causes one or more compressed partitions to become part of the table.This does not apply to a partitioned table having B-tree indexes only.

This rebuilding of the bitmap index structures is necessary to accommodate the
potentially higher number of rows stored for each data block with table compression
enabled and must be done only for the first time. All subsequent operations, whether
they affect compressed or uncompressed partitions, or change the compression
attribute, behave identically for uncompressed, partially compressed, or fully
compressed partitioned tables.

To avoid the recreation of any bitmap index structure, Oracle recommends creating
every partitioned table with at least one compressed partition whenever you plan to
partially or fully compress the partitioned table in the future. This compressed
partition can stay empty or even can be dropped after the partition table creation.
Having a partitioned table with compressed partitions can lead to slightly larger
bitmap index structures for the uncompressed partitions. The bitmap index structures
for the compressed partitions, however, are in most cases smaller than the appropriate bitmap index structure before table compression. This highly depends on the achieved compression rates.

Example of Table Compression and Partitioning
The following statement moves and compresses an already existing partition sales_
q1_1998 of table sales:
ALTER TABLE sales
MOVE PARTITION sales_q1_1998 TABLESPACE ts_arch_q1_1998 COMPRESS;

If you use the MOVE statement, the local indexes for partition sales_q1_1998 become
unusable. You have to rebuild them afterward, as follows:

ALTER TABLE sales
MODIFY PARTITION sales_q1_1998 REBUILD UNUSABLE LOCAL INDEXES;

The following statement merges two existing partitions into a new, compressed
partition, residing in a separate tablespace. The local bitmap indexes have to be rebuilt afterward, as follows:

ALTER TABLE sales MERGE PARTITIONS sales_q1_1998, sales_q2_1998
INTO PARTITION sales_1_1998 TABLESPACE ts_arch_1_1998
COMPRESS UPDATE INDEXES;

Posted in: Partitioning