create table 节约成本中重要的属性 compress 详解

Usually, the application developer is responsible for designing the elements of an application, including the tables. Database administrators are responsible for establishing the attributes of the underlying tablespace that will hold the application tables. Either the DBA or the applications developer, or both working jointly, can be responsible for the actual creation of the tables, depending upon the practices for a site.

Working with the application developer, consider the following guidelines when designing tables:

• Use descriptive names for tables, columns, indexes, and clusters.

• Be consistent in abbreviations and in the use of singular and plural forms of table names and columns.

• Document the meaning of each table and its columns with the COMMENT command.

• Normalize each table.

• Select the appropriate datatype for each column.

• Consider whether your applications would benefit from adding one or more virtual columns to some tables.

• Define columns that allow nulls last, to conserve storage space.

• Cluster tables whenever appropriate, to conserve storage space and optimize performance of SQL statements.

Before creating a table, you should also determine whether to use integrity constraints. Integrity constraints can be defined on the columns of a table to enforce the business rules of your database automatically.

Oracle至少有六种压缩的方法:

1. BASIC compression, introduced in Oracle 8 already and only recommended for Data Warehouse
2. OLTP compression, introduced in Oracle 11 and recommended for OLTP Databases as well
3. QUERY LOW compression (Exadata only), recommended for Data Warehouse with Load Time as a critical factor
4. QUERY HIGH compression (Exadata only), recommended for Data Warehouse with focus on Space Saving
5. ARCHIVE LOW compression (Exadata only), recommended for Archival Data with Load Time as a critical factor
6. ARCHIVE HIGH compression (Exadata only), recommended for Archival Data with maximum Space Saving

NORMAL方式：

CREATE TABLE ml_test_normal AS SELECT * FROM all_objects

 

BASIC方式：

SQL> create table ml_test_basic compress as select * from all_objects where 1=2;  Table created Executed in 0.281 seconds SQL> insert /*+ append */ into ml_test_basic select * from all_objects; 1499285 rows insertedExecuted in 13.441 secondsSQL> commit; Commit complete Executed in 0 seconds

与下面 对比

SQL> create table ml_test_normal  as select * from all_objects where 1=2;  Table created SQL> insert into ml_test_normal SELECT * FROM all_objects; 1499286 rows inserted SQL> set timi onSQL> insert into ml_test_normal SELECT * FROM all_objects; 1499286 rows inserted Executed in 15.584 seconds SQL> 

BASIC方式，但是没有使用append

SQL> create table ml_test_basicnotappend compress nologging as select * from all_objects where 1=2; Table created Executed in 0.281 seconds SQL> insert into ml_test_basicnotappend  SELECT * FROM all_objects; 1499288 rows inserted Executed in 12.168 seconds SQL> 

 OLTP方式：（只适合oltp）

SQL> create table ml_test_oltp compress for oltp as select * from all_objects where 1=2;  Table created Executed in 0.281 seconds SQL> insert /*+ append */ into  ml_test_oltp select * from all_objects; 1499287 rows inserted Executed in 16.848 seconds SQL> commit; Commit complete Executed in 0.016 seconds SQL> 

 

QUERY LOW ，QUERY HIGH ，ARCHIVE LOW，ARCHIVE HIGH 只适合exadata数据库

SQL> create table ml_test_query_low compress for query low as select * from all_objects where 1=2; create table ml_test_query_low compress for query low as select * from all_objects where 1=2 ORA-64307: 只有位于 Exadata 存储上的表空间才支持混合柱状压缩 SQL> create table ml_test_query_high compress for query high as select * from all_objects where 1=2;  create table ml_test_query_high compress for query high as select * from all_objects where 1=2 ORA-64307: 只有位于 Exadata 存储上的表空间才支持混合柱状压缩 

SQL> create table ml_test_archive_low compress for archive low as select * from all_objects where 1=2;  create table ml_test_archive_low compress for archive low as select * from all_objects where 1=2 ORA-64307: 只有位于 Exadata 存储上的表空间才支持混合柱状压缩

以上测试结果：

SQL> select segment_name,bytes/1024/1024 as mb from user_segments  2  where segment_Name IN ('ML_TEST_BASIC','ML_TEST_OLTP','ML_TEST_NORMAL','ML_TEST_BASICNOTAPPEND')  3  order by 2; SEGMENT_NAME                                       MB--------------------------------------------- ----------ML_TEST_BASIC                                      41ML_TEST_OLTP                                       46ML_TEST_BASICNOTAPPEND                            192ML_TEST_NORMAL                                    208 Executed in 0.437 seconds SQL> 

总结：

下面我们系统准备使用basic方式，：进一步做以下测试，方便使用：

SQL> ALTER TABLE ml_test_basicnotappend ADD (ml NUMBER DEFAULT 0); ALTER TABLE ml_test_basicnotappend ADD (ml NUMBER DEFAULT 0) ORA-39726: 不支持对压缩表执行添加/删除列操作 SQL>  ALTER TABLE ml_test_basicnotappend ADD (ml NUMBER ); Table altered Executed in 0.125 seconds SQL>  ALTER TABLE ml_test_basicnotappend ADD (ml2 NUMBER default 0 not null); Table altered Executed in 0.141 seconds

SQL> alter table  ml_test_basicnotappend drop column ml2; alter table  ml_test_basicnotappend drop column ml2 ORA-39726: 不支持对压缩表执行添加/删除列操作

对compress的表的列，可以设置unused（不参与解压，压缩操作。减少cpu负载，io），但是不能彻底删除。，当使用exchange属性的时候请注意哦