compress table (转)

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
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以获得最大的压缩比。