Data in a partitioned table can be compressed on a partition-by-partition basis. Using compressed data is most efficient for data that does not change frequently. Common data warehouse scenarios often see few data changes as data ages and other scenarios only insert data. Using the partition management features, you can compress data on a partition-by-partition basis. Although Oracle Database supports compression for all DML operations, it is still more efficient to modify data in a noncompressed table.
Altering a partition to enable compression applies only to future data to be inserted into the partition. To compress the existing data in the partition, you must move the partition. Enabling compression and moving a partition can be done in a single operation.
To use table compression on partitioned tables with bitmap indexes, you must do the following before you introduce the compression attribute for the first time:
Mark bitmap indexes UNUSABLE.
Set the compression attribute.
Rebuild the indexes.
The first time you make a compressed partition part of an existing, fully uncompressed partitioned table, you must either drop all existing bitmap indexes or mark them UNUSABLE before adding a compressed partition. This must be done regardless of whether any partition contains 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 only B-tree indexes.
The following example shows how to compress the SALES_1995 partition in the sales table:
ALTER TABLE sales MOVE PARTITION sales_1995 COMPRESS FOR OLTP PARALLEL NOLOGGING;
If a table or a partition takes less space on disk, then the performance of large table scans in an I/O-constraint environment may improve.