oracle从9i r2开始推出了compress table的功能，compress table能提供良好的压缩性能，十分适用于存储历史数据。的打算

 

compress table需要通过创建table时指定compress子句

 

SQL 9I>create table testcom3(a number) compress;

Table created.

需要通过批量导入数据才能实现compress

 

1.alter table move
2.create table as select
3.insert /*+ APPEND */
4.direct path sqlldr

下面来看一些例子

 

SQL 9I>create table test(a varchar2(10),b number);

Table created.

begin
for i in 1..1000 loop
insert into test values(to_char(mod(i,9)),i);
commit;
end loop;
end;
/

PL/SQL procedure successfully completed.

SQL 9I>create table testcom1 compress as select * from test order by a;

Table created.

SQL 9I>set serveroutput on
SQL 9I>exec show_space('TEST');
Unformatted Blocks .....................              32
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               1
FS4 Blocks (75-100).....................              26
Full Blocks        .....................               1
Total Blocks............................             128
Total Bytes.............................       1,048,576
Total MBytes............................               1
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................              11
Last Used Ext BlockId...................             904
Last Used Block.........................              64

PL/SQL procedure successfully completed.

SQL 9I>exec show_space('TESTCOM1');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................               2
Total Blocks............................             128
Total Bytes.............................       1,048,576
Total MBytes............................               1
Unused Blocks...........................             122
Unused Bytes............................         999,424
Last Used Ext FileId....................              11
Last Used Ext BlockId...................           1,032
Last Used Block.........................               6

PL/SQL procedure successfully completed.

可以看到compress table提供了良好的压缩比

 

另外创建compress table的时候还需要注意的是order by子句的功能

 

 

create table test2(a varchar2(10),b varchar2(10),c varchar2(10));

 

 

begin
for i in  1000000000..1000100000 loop
insert into test2 values(i,'1',to_char(mod(i,100)));
commit;
end loop;
end;
/

create table testcom4 compress as select * from test2 order by c;

 

create table testcom5 compress as select * from test2;

 

SQL 9I>exec show_space('TEST2');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              24
Full Blocks        .....................             288
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................              13
Last Used Ext BlockId...................           1,032
Last Used Block.........................              64

PL/SQL procedure successfully completed.

SQL 9I>exec show_space('TESTCOM4');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             226
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              24
Unused Bytes............................         196,608
Last Used Ext FileId....................              12
Last Used Ext BlockId...................           1,160
Last Used Block.........................             104

PL/SQL procedure successfully completed.

 

 

SQL 9I>exec show_space('TESTCOM5');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             259
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................             117
Unused Bytes............................         958,464
Last Used Ext FileId....................              13
Last Used Ext BlockId...................           1,160
Last Used Block.........................              11

PL/SQL procedure successfully completed.

 

 

可见order by子句对compress影响也是比较大，我们应该指定重复值多并且长度大的列做order by以获得最大的压缩比。

 

有人会对compress的读写性能表示担忧，但是实际上无论是全表扫描还是通过索引回表扫描压缩表的性能都不会比非压缩表差。至于dml，压缩表应该是不推荐进行dml的，但是当你通过非bulk操作inert 数据时那么这些数据将会不会进行压缩存储，也就是按照普通格式操作，所以效率并不会低，但是还是要避免对压缩表进行dml操作，尤其是update，update将会导致行迁移，从而使压缩表的容量比非压缩表还要大。

 

 

全表扫描返回记录数对比：

select count(*)  from test2

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.21       0.21        313        315          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.21       0.21        313        315          0           1

 

 

select count(*)  from testcom4

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.20       0.20        227        229          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.20       0.20        227        229          0           1

 

全表扫描返回全部数据对比：

 

select * from test2

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       22      0.30       0.29        313        335          0      100001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       24      0.30       0.29        313        335          0      100001

 

select *  from testcom4

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       22      0.29       0.26        227        249          0      100001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       24      0.29       0.26        227        249          0      100001

 

 

 

索引回表对比：

 

ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
ALTER SESSION SET EVENTS 'immediate trace name flush_cache';
alter session set events'10046 trace name context forever,level 1';
declare
    l_rec test2%rowtype;
begin
    for i in 1000000000..1000100000
    loop
        select * into l_rec from test2 where a = to_char(i);
        select * into l_rec from testcom4 where a = to_char(i);
    end loop;
end;
/
alter session set events'10046 trace name context off';

 

SELECT * from test2 where a = to_char(:b1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100001      1.95       1.76          0          0          0           0
Fetch   100001      2.84       2.69        597     300309          0      100001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200003      4.79       4.45        597     300309          0      100001

 

 

SELECT * from testcom4 where a = to_char(:b1)

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 100001      1.56       1.76          0          0          0           0
Fetch   100001      3.15       2.76        534     300309          0      100001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   200003      4.71       4.53        534     300309          0      100001

 

 

 

select * from test2 order by c

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       22      0.61       0.61        313        315          0      100001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       24      0.61       0.61        313        315          0      100001

 

Rows     Row Source Operation
-------  ---------------------------------------------------
 100001  SORT ORDER BY (cr=315 r=313 w=0 time=466969 us)
 100001   TABLE ACCESS FULL TEST2 (cr=315 r=313 w=0 time=125852 us)

 

select * from testcom4 order by c

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       22      0.51       0.50        227        229          0      100001
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       24      0.51       0.51        227        229          0      100001

 

Rows     Row Source Operation
-------  ---------------------------------------------------
 100001  SORT ORDER BY (cr=229 r=227 w=0 time=369099 us)
 100001   TABLE ACCESS FULL TESTCOM4 (cr=229 r=227 w=0 time=120071 us)

 

select count(*) from test2 group by c

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        8      0.30       0.28        313        315          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.30       0.29        313        315          0         100

 

select count(*) from testcom4 group by c

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        8      0.25       0.24        227        229          0         100
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       10      0.25       0.24        227        229          0         100

 

 

 

SQL 9I>update testcom4 set c=c;

100001 rows updated.

SQL 9I>commit;

Commit complete.

SQL 9I>exec show_space('TESTCOM4');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              23
Full Blocks        .....................             857
Total Blocks............................             896
Total Bytes.............................       7,340,032
Total MBytes............................               7
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              29
Last Used Ext BlockId...................          22,792
Last Used Block.........................             128

PL/SQL procedure successfully completed.

 

SQL 9I>alter table testcom4 move compress;

Table altered.

 

 

SQL 9I>exec show_space('TESTCOM4');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               0
Full Blocks        .....................             226
Total Blocks............................             256
Total Bytes.............................       2,097,152
Total MBytes............................               2
Unused Blocks...........................              24
Unused Bytes............................         196,608
Last Used Ext FileId....................              12
Last Used Ext BlockId...................           1,416
Last Used Block.........................             104

PL/SQL procedure successfully completed.

SQL 9I>update testcom4 set a=substr(a,1,4);

100001 rows updated.

SQL 9I>commit;

Commit complete.

SQL 9I>exec show_space('TESTCOM4');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................               2
Full Blocks        .....................             814
Total Blocks............................             896
Total Bytes.............................       7,340,032
Total MBytes............................               7
Unused Blocks...........................              64
Unused Bytes............................         524,288
Last Used Ext FileId....................              29
Last Used Ext BlockId...................          22,792
Last Used Block.........................              64

PL/SQL procedure successfully completed.

 

update导致testcom4的容量从2,097,152－>7,340,032,甚至比原始表还要大很多。

 

 

 

下面来看一下compress table的内部存储格式

 

        r0_9ir2=0x0
        mec_kdbh9ir2=0x3
        r1_9ir2=0x0
                  76543210
        flag_9ir2=------OC
                fcls_9ir2[4]={ 0 32768 32768 10 }
                perm_9ir2[3]={ 2 0 1 } 下面tab1中col n对应表实际列顺序，tab 1中的col2对应表的col 0(A),col 0对应表的col1(B)，col 1对应表的col2(c)
0x22:pti[0]     nrow=1  offs=0   tab 0中有3行，tab 0是一个标记表，记录压缩的列，以下的tab 0记录了压缩过的b,c列重复值，一共有1组。
0x26:pti[1]     nrow=444   offs=1 记录tab 1也就是存储非压缩列的数据，在这里是A列，一共拥有444条。
tab 0, row 0, @0x1f79
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  31 列B值为'1'
col  1: [ 1]  30 列C值为'0'
bindmp: 01 bc 02 c9 31 c9 30  01bc＝444，这个压缩项提供给444行使用
tab 1, row 0, @0x1f69
tl: 16 fb: --H-FL-- lb: 0x0  cc: 3
col  0: [ 1]  31
col  1: [ 1]  30
col  2: [10]  31 30 30 30 30 31 35 30 30 30
bindmp: 2c 00 02 03 00 c9 31 30 30 30 30 31 35 30 30 30

bindmp里面是实际储存的值，包含指向tab 0的指针和A列的值。

 

最后再来看一下update后数据块内部发生了什么变化

SQL 9I>update testcom4 set c=c;

100001 rows updated.

 

SQL 9I>commit;

Commit complete.

 

SQL 9I>exec show_space('TESTCOM4');
Unformatted Blocks .....................               0
FS1 Blocks (0-25)  .....................               0
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              23
Full Blocks        .....................             857
Total Blocks............................             896
Total Bytes.............................       7,340,032
Total MBytes............................               7
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................              29
Last Used Ext BlockId...................          22,792
Last Used Block.........................             128

PL/SQL procedure successfully completed.

 

SQL 9I>select header_file,header_block from dba_segments where segment_name='TESTCOM4';

HEADER_FILE HEADER_BLOCK
----------- ------------
         11         2188

 

SQL 9I>alter system dump datafile 11 block 2189;

System altered.

 

tab 0, row 0, @0x1f79
tl: 7 fb: --H-FL-- lb: 0x0  cc: 2
col  0: [ 1]  31
col  1: [ 1]  30
bindmp: 01 bc 02 c9 31 c9 30
tab 1, row 0, @0x1f69
tl: 9 fb: --H----- lb: 0x2  cc: 0
nrid:  0x030004fa.0
bindmp: 20 02 00 03 00 04 fa 00 00

发生了行迁移，导致了testcom4容量大增,所以切忌对compress table进行update操作，一定把compress table用到合适的地方。