compress表段壓縮基礎記載

從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.

[@more@]