一.压缩在存储的表现

--相同的表结构和数据
A>create table t_comp compress as select object_id,object_name from all_objects;

Table created.

A>create table t_nocomp as select object_id,object_name from all_objects;

Table created.

A>select SEGMENT_NAME,sum(BYTES) as "size",sum(BLOCKS) as "blocks" from user_extents group by SEGMENT_NAME;

SEGMENT_NAME               size     blocks
-------------------- ---------- ----------
T_NOCOMP                3145728        384
T_COMP                  2097152        256

--在相同字段上建索引
A>create index ind_t_comp on t_comp(object_name) compress;

Index created.

A>create index ind_t_nocomp on t_nocomp(object_name);

Index created.

A>select SEGMENT_NAME,sum(BYTES) as "size",sum(BLOCKS) as "blocks" from user_extents group by SEGMENT_NAME order by 1;

SEGMENT_NAME               size     blocks
-------------------- ---------- ----------
IND_T_COMP              2097152        256
IND_T_NOCOMP            3145728        384
T_COMP                  2097152        256
T_NOCOMP                3145728        384

--压缩的优势在重复率低，或者惟一值高时才明显
A>create table t1 as select 1 object_id,object_name from all_objects;

Table created.

A>create table t2 as select object_id,object_name from all_objects;

Table created.

A>create index ind_t1 on t1(object_id) compress;

Index created.

A>create index ind_t2 on t2(object_id) compress;

Index created.

A>select SEGMENT_NAME,sum(BYTES) as "size",sum(BLOCKS) as "blocks" from user_extents where SEGMENT_NAME like '%IND%' group by SEGMENT_NAME ;

SEGMENT_NAME               size     blocks
-------------------- ---------- ----------
IND_T2                  2097152        256
IND_T1                   851968        104

二.压缩在查询性能上的表现
（为了得到以下结果，尤其是disk部分，我在每次sql_trace前都先执行一次alter system flush buffer_cache）

1.全表扫描
select object_id
from
t_comp

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3846      0.43       2.49        246       4082          0       57673
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total     3848      0.43       2.50        246       4082          0       57673

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows Row Source Operation
------- ---------------------------------------------------
57673 TABLE ACCESS FULL T_COMP (cr=4082 pr=246 pw=0 time=188395 us cost=73 size=746031 card=57387)

select object_id
from
t_nocomp

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3846      0.66       4.68        822       4620          0       57674
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total     3848      0.66       4.69        822       4620          0       57674

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows Row Source Operation
------- ---------------------------------------------------
57674 TABLE ACCESS FULL T_NOCOMP (cr=4620 pr=822 pw=0 time=13804096 us cost=231 size=691457 card=53189)

2.排序
select object_id
from
t_comp order by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.03       0.04          1          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3846      0.32       1.00         81        249          0       57673
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total     3848      0.35       1.05         82        250          0       57673

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows Row Source Operation
------- ---------------------------------------------------
57673 SORT ORDER BY (cr=249 pr=81 pw=0 time=90224 us cost=546 size=1721610 card=57387)
57673 TABLE ACCESS FULL T_COMP (cr=249 pr=81 pw=0 time=162402 us cost=73 size=1721610 card=57387)

select object_id
from
t_nocomp order by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.03       0.03          1          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     3846      0.67       4.58        564        826          0       57674
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total     3848      0.70       4.61        565        827          0       57674

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows Row Source Operation
------- ---------------------------------------------------
57674 SORT ORDER BY (cr=826 pr=564 pw=0 time=221079 us cost=670 size=1595670 card=53189)
57674 TABLE ACCESS FULL T_NOCOMP (cr=826 pr=564 pw=0 time=151919 us cost=231 size=1595670 card=53189)

3.分组查询
select owner,count(*)
from
t_comp group by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.29       1.64        246        249          0          13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.29       1.64        246        249          0          13

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows     Row Source Operation
------- ---------------------------------------------------
     13 HASH GROUP BY (cr=249 pr=246 pw=0 time=0 us cost=77 size=975579 card=57387)
57673   TABLE ACCESS FULL T_COMP (cr=249 pr=246 pw=0 time=127620 us cost=73 size=975579 card=57387)

select owner,count(*)
from
t_nocomp group by owner

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.70       6.60        822        826          0          13
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.70       6.60        822        826          0          13

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows     Row Source Operation
------- ---------------------------------------------------
     13 HASH GROUP BY (cr=826 pr=822 pw=0 time=0 us cost=235 size=904213 card=53189)
57674   TABLE ACCESS FULL T_NOCOMP (cr=826 pr=822 pw=0 time=17924282 us cost=231 size=904213 card=53189)

4.模糊查询
select object_id
from
t_comp where owner like 'A%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.03       0.08          1          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.16       0.82         81        250          0           8
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.19       0.90         82        251          0           8

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows Row Source Operation
------- ---------------------------------------------------
8 TABLE ACCESS FULL T_COMP (cr=250 pr=81 pw=0 time=0 us cost=73 size=90 card=3)

select object_id
from
t_nocomp where owner like 'A%'

call     count       cpu    elapsed       disk      query    current        rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse        1      0.01       0.06          1          1          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.62       5.87        564        827          0           9
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total        4      0.64       5.93        565        828          0           9

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 59

Rows Row Source Operation
------- ---------------------------------------------------
9 TABLE ACCESS FULL T_NOCOMP (cr=827 pr=564 pw=0 time=0 us cost=231 size=270 card=9)

可以看到压缩表在各个方面都全线胜出，之所以这样，只有一点——那就是压缩后，块少了

三.压缩和DML的关系
1.insert
常规插入时忽略压缩属性

A>create table t_comp2 compress as select * from all_objects where 1=0;

Table created.

A>insert into t_comp2 select * from all_objects;

57675 rows created.