从oracle9IR2开始，oracle推出compress技术，压缩表中重复的数据，在数据块级别生效，压缩表会在数据块上的相同数据上存储指向保留空间符号表（symbol table）的指针，相同的数据其实真正存储在符号表内。

表段压缩技术可以有效减少IO访问，更多适用于数据仓库中，compress表段的访问也需要消耗cpu，用于解析压缩的数据行，多用于只读少量更新的表段

SQL> create table t_comp01 as select rownum id ,a.* from dba_objects a;

Table created

SQL> insert into t_comp01 select * from t_comp01;

SQL> /;

78477 rows inserted

SQL> insert into t_comp01 select * from t_comp01;

156954 rows inserted

SQL> commit;

构造一个大表t_comp01，下面分别采取compress和默认的nocompress的创建表

SQL> create table t_comp02 compress as select * from t_comp01;

Table created

SQL> create table t_comp03 as select * from t_comp01;

Table created

对三张表分别进行分析。

SQL> analyze table t_comp01 compute statistics;

Table analyzed

SQL> analyze table t_comp02 compute statistics;

Table analyzed

SQL> analyze table t_comp03 compute statistics;

Table analyzed

查看所占用的blocks

SQL> select num_rows,blocks,empty_blocks,table_name from user_tables where table_name like 'T_COMP%';

NUM_ROWS BLOCKS EMPTY_BLOCKS TABLE_NAME

---------- ---------- ------------ ------------------------------

313908 4554 54 T_COMP01

313908 1701 91 T_COMP02

313908 4584 24 T_COMP03

可以看出采取compress创建的表段仅仅只是用了1701的blocks，那么相应的如果做fts等执行计划，相对来说对数据块的访问会大量减少，可以有效的减少fts的IO消耗。

SQL> truncate table t_comp02;

Table truncated

SQL> truncate table t_comp03;

Table truncated

SQL> insert into t_comp02 select * from t_comp01;

313908 rows inserted

SQL> insert into t_comp03 select * from t_comp01;

313908 rows inserted

SQL> analyze table t_comp02 compute statistics;

Table analyzed

SQL> analyze table t_comp03 compute statistics;

Table analyzed

SQL> select num_rows,blocks,empty_blocks,table_name from user_tables where table_name like 'T_COMP%';

NUM_ROWS BLOCKS EMPTY_BLOCKS TABLE_NAME

---------- ---------- ------------ ------------------------------

313908 4554 54 T_COMP01

313908 4150 74 T_COMP02

313908 4528 80 T_COMP03

39 5 0 T_COMPAR_ORG

可以看出普通的插入数据并不会把数据进行compress，而下面的append直接插入才会对数据进行compress。

SQL> truncate table t_comp03;

Table truncated

SQL> truncate table t_comp02;

Table truncated

SQL> insert into t_comp03 select * from t_comp01;

313908 rows inserted

SQL> insert /*+append*/into t_comp02 select * from t_comp01;

313908 rows inserted

SQL> analyze table t_comp02 compute statistics;

Table analyzed

SQL> analyze table t_comp03 compute statistics;

Table analyzed

SQL> select num_rows,blocks,empty_blocks,table_name from user_tables where table_name like 'T_COMP%';

NUM_ROWS BLOCKS EMPTY_BLOCKS TABLE_NAME

---------- ---------- ------------ ------------------------------

313908 4554 54 T_COMP01

313908 1701 91 T_COMP02

313908 4528 80 T_COMP03

39 5 0 T_COMPAR_ORG

这里下面采取parallel方式插入，其实如果制定了parallel的dml也就会采取append方式插入，这里也对之前我的理解parallel方式不包含append的方式解惑了。

SQL> truncate table t_comp02;

Table truncated

SQL> truncate table t_comp03;

Table truncated

SQL> alter session enable parallel dml;

Session altered

SQL> insert /*+parallel(t_comp02 2)*/into t_comp02 select * from t_comp01;

313908 rows inserted

SQL> insert into t_comp03 select * from t_comp01;

313908 rows inserted

SQL> analyze table t_comp02 compute statistics;

Table analyzed

SQL> analyze table t_comp03 compute statistics;

Table analyzed

SQL> select num_rows,blocks,empty_blocks,table_name from user_tables where table_name like 'T_COMP%';

NUM_ROWS BLOCKS EMPTY_BLOCKS TABLE_NAME

---------- ---------- ------------ ------------------------------

313908 4554 54 T_COMP01

313908 1976 0 T_COMP02

313908 4528 80 T_COMP03

39 5 0 T_COMPAR_ORG

Append、parallel，ctas的compress和sqlldr load都会启用表段压缩技术，而普通的dml却不会在compress表段上生效。

这里补充一点：9i下压缩的表是无法add column的，10g下就可以了，会出现所谓的

[oracle@server127 ~]$ oerr ora 22856

22856, 00000, "cannot add columns to object tables"

// *Cause: An attempt was made to add columns to an object table. Object

// tables cannot be altered to add columns since its

// definition is based on an object type.

// *Action: Create a new type with additional attributes, and use the new

// type to create an object table. The new object table will have

// the desired columns.