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