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