oracle表压缩比,oracle的compress 特性介绍

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   --compress是可以的，从compressed表是无法ctas为nocompress

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将会导致行迁移，从而使压缩表的容量比非压缩表还要大。