create table進階學習系列(九)

---語法
{ 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.