Table compression was introduced in Oracle 9i as a space saving feature for data warehousing projects. In 11g it is now considered a mainstream feature that is acceptable for OLTP databases. In addition to saving storage space, compression can result in increased I/O performance and reduced memory use in the buffer cache. These advantages do come at a cost, since compression incurs a CPU overhead, so it won't be of benefit to everyone.

Oracle 表压缩有表空间级别、 表或分区级别：

• NOCOMPRESS - 表和分区不进行压缩。 在不进行任何指定情况下默认建表不会进行压缩。

• COMPRESS - 这个选项适用于数据仓库系统。 表或分区在进行direct-path 插入时会进行压缩。

• COMPRESS FOR DIRECT_LOAD OPERATIONS - 这个和COMPRESS 的功能一样。

• COMPRESS FOR ALL OPERATIONS - 这个选项使用于OLTP 系统。 这个选项会为所有的操作进行压缩，包括常规的DML操作。此选项要11.1.0 或更高版本。 在11gR2 中此选项重命名为COMPRESS FOR OLTP。

Table 级别和Partition级别的压缩示例：

-- Table compression.

create table test_tab_1(

id                   number(10)   not null,

description    varchar2(50)  not null,

create_date   date               not null

)

compress for all operations;

--Partition level compression.

create table test_tab_2(

id                  number(10)   not null,

description   varchar2(50)  not null,

create_date   date              not null

)

partition by range (create_date)(

   partition test_tab_q1 values less than (to_date('01/01/2018','DD/MM/YYYY')) compress,

   partition test_tab_q2 values less than (to_date('01/04/2018','DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,

   partition test_tab_q3 values less than (to_date('01/07/2018','DD/MM/YYYY')) compress for all operations,

   partition test_tab_q4 values less than (maxvalue) nocompress

   );

表级别的压缩可以通过[DBA|ALL|USER]_TABLES 视图去查询，对于分区表不会有显示是否进行了压缩。

select table_name,compression,compress_for from user_tables;

分区级别的压缩可以通过[DBA|ALL|USER]_TAB_PARTITIONS 查看。

select table_name,partition_name,compression,compress_for from user_tab_partitions;

对于表和分区的压缩设置可以通过alter table 命令去修改。 这种改变对现有的数据无影响，仅对新的操作有效。

alter table test_tab_1 nocompress;

alter table test_tab_2 modify partition test_tab_q4 compress for all operations;

表压缩的限制：

The restrictions associated with table compression include:

• Compressed tables can only have columns added or dropped if the  COMPRESS FOR ALL OPERATIONS option was used.
• Compressed tables must not have more than 255 columns.
• Compression is not applied to lob segments.
• Table compression is only valid for heap organized tables, not index organized tables.
• The compression clause cannot be applied to hash or hash-list partitions. Instead, they must inherit their compression settings from the tablespace, table or partition settings.
• Table compression cannot be specified for external or clustered tables.

参考链接：

https://oracle-base.com/articles/11g/table-compression-enhancements-11gr1#:~:text=Table%20compression%20was%20introduced%20in%20Oracle%209i%20as,and%20reduced%20memory%20use%20in%20the%20buffer%20cache.