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 表壓縮有表空間級別、 表或分割槽級別：

• 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.

參考連結：

~:text=Table%20compression%20was%20introduced%20in%20Oracle%209i%20as,and%20reduced%20memory%20use%20in%20the%20buffer%20cache.