Compressed Tablespaces

You can specify that all tables created in a tablespace are compressed by default. You specify the type of table compression using the DEFAULT keyword, followed by one of the compression type clauses used when creating a table.

The following statement indicates that all tables created in the tablespace are to use OLTP compression, unless otherwise specified:

CREATE TABLESPACE ... DEFAULT COMPRESS FOR OLTP ... ;

Table Compression Methods --(注：COMPRESS FOR QUERY,ARCHIVE選項需使用EXADATA硬體)

Table Compression Method	Compression Level	CPU Overhead	Applications	Notes
Basic compression	High	Minimal	DSS	None.
OLTP compression	High	Minimal	OLTP, DSS	None.
Warehouse compression (Hybrid Columnar Compression)	Higher	Higher	DSS	The compression level and CPU overhead depend on compression level specified (LOW or HIGH).
Online archival compression (Hybrid Columnar Compression)	Highest	Highest	Archiving	The compression level and CPU overhead depend on compression level specified (LOW or HIGH).

Table Compression Method	CREATE/ALTER TABLE Syntax	Direct-Path INSERT	Notes
Basic compression	COMPRESS [BASIC]	Rows are compressed with basic compression.	COMPRESS and COMPRESS BASIC are equivalent. Rows inserted without using direct-path insert and updated rows are uncompressed.
OLTP compression	COMPRESS FOR OLTP	Rows are compressed with OLTP compression.	Rows inserted without using direct-path insert and updated rows are compressed using OLTP compression.
Warehouse compression (Hybrid Columnar Compression)	COMPRESS FOR QUERY [LOW\|HIGH]	Rows are compressed with warehouse compression.	This compression method can result in high CPU overhead. Rows inserted without using direct-path insert and updated rows go to a block with a less compressed format and have lower compression level.
Online archival compression (Hybrid Columnar Compression)	COMPRESS FOR ARCHIVE [LOW\|HIGH]	Rows are compressed with online archival compression.	This compression method can result in high CPU overhead. Rows inserted without using direct-path insert and updated rows go to a block with a less compressed format and have lower compression level.

以上參考

另預估計算table被壓縮資料率可使用DBMS_COMPRESSION，參考：

http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_compress.htm#BEIHIJBI

Oracle Table and tablespace Compress

相關文章