---语法
{ COMPRESS [ BASIC
           | FOR { OLTP
                 | { QUERY | ARCHIVE } [ LOW | HIGH ]
                 }
           ]
| NOCOMPRESS
}

--测试

---默认非压缩建表
SQL> create table t_test(a int);
 
Table created
 
 
SQL> insert into t_test select level from dual connect by level<=9e5;
 
900000 rows inserted
 
SQL> insert into t_test select level from dual connect by level<=9e5;
 
900000 rows inserted
 
SQL> commit;
 
Commit complete
 
---未压缩表大小
SQL> select bytes/1024/1024 mb from user_segments where segment_name='T_TEST';
 
        MB
----------
        22
 
SQL> drop table t_test purge;
 
Table dropped

--- compress basic用于direct-path insert情景下压缩数据,imp不支持direct-path insert,故不能导入压缩格式的数据
--- 使用pctfree为0最大化压缩数据
--- 早期版本,叫作dss table压缩,用compress for direct_load开启,此语法已deprectated
SQL> create table t_test(a int) compress basic;
 
Table created
 
SQL> insert into t_test select level from dual connect by level<=9e5;
 
900000 rows inserted
 
SQL> insert into t_test select level from dual connect by level<=9e5;
 
900000 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> select bytes/1024/1024 mb from user_segments where segment_name='T_TEST';
 
        MB
----------
        20
 
SQL> drop table t_test purge;
 
Table dropped
 
---compress for oltp,用于oltp环境,pctfree为10,适用于dml表时压缩数据
SQL> create table t_test(a int) compress for oltp;
 
Table created
 
SQL> insert into t_test select level from dual connect by level<=9e5;
 
900000 rows inserted
 
SQL> insert into t_test select level from dual connect by level<=9e5;
 
900000 rows inserted
 
SQL> commit;
 
Commit complete
 
SQL> select bytes/1024/1024 mb from user_segments where segment_name='T_TEST';
 
        MB
----------
        22
 
SQL> drop table t_test purge;
 
Table dropped
 
--而如下的compress for query和compress for archive适用于Exadata一体机存储情景,如下测试也证实此点
SQL> create table t_test(a int) compress for query low;
 
create table t_test(a int) compress for query low
 
ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage
 
SQL> create table t_test(a int) compress for archive low;
 
create table t_test(a int) compress for archive low
 
ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage

---附上compress for query和compress for archive的语义
?When you specify COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE, you enable Hybrid Columnar Compression. With Hybrid Columnar Compression,
data can be compressed during bulk load operations. During the load process, data is transformed into a column-oriented format and then compressed.
Oracle Database uses a compression algorithm appropriate for the level you specify. In general, the higher the level, the greater the compression ratio.
Hybrid Columnar Compression can result in higher compression ratios, at a greater CPU cost. Therefore, this form. of compression is recommended for data that
is not frequently updated.
 
COMPRESS FOR QUERY is useful in data warehousing environments. Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH.
 
COMPRESS FOR ARCHIVE uses higher compression ratios than COMPRESS FOR QUERY, and is useful for compressing data that will be stored for long periods of time.
Valid values are LOW and HIGH, with HIGH providing the highest possible compression ratio. The default is LOW.
 
Tables with COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE use a PCTFREE value of 0 to maximize compression, unless you explicitly set a value for PCTFREE in the
physical_attributes_clause. For these tables, PCTFREE has no effect for blocks loaded using direct-path INSERT. PCTFREE is honored for blocks loaded using
conventional INSERT, and for blocks created as a result of DML operations on blocks originally loaded using direct-path INSERT.