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. |
以上參考
http://docs.oracle.com/cd/E11882_01/server.112/e25494/tspaces.htm#CIHGCFBB
另預估計算table被壓縮數據率可使用DBMS_COMPRESSION，參考：
http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_compress.htm#BEIHIJBI
http://www.oracledatabase12g.com/archives/11g-compression-%E6%96%B0%E7%89%B9%E6%80%A7.html