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 inserted

Executed in 13.441 seconds

SQL> 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 on
SQL> 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                                      41
ML_TEST_OLTP                                       46
ML_TEST_BASICNOTAPPEND                            192
ML_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属性的时候请注意哦