Oracle 的表压缩

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 表压缩有表空间级别、 表或分区级别：

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: