Oracle11g增加了很多压缩的功能，如对OLTP操作下表压缩的完善，Data Pump 文件的压缩，standby日志传输的压缩，RMAN备份的压缩。
10g之前指定表的compress选项，只能在direct path load或者table move的时候进行压缩
11g中指定compress for all operations则增加了对表压缩的条件。如orawh所总结，
oracle改变了压缩的行为，不是每次发生数据变化都会去压缩，而是通过内部的threshold来控制压缩，并且压缩是由transaction触发的，当一个transaction触发DML操作，oracle会去根据threshold来判断是否需要对整个BLOCK进行compress，如果compress后又到达threshold，那么oracle会再去recompress整个BLOCK,直到oracle觉得没有可压缩的余地，而且只有触发compress的transaction会有一些压缩的代价。
测试如下，
[coolcode lang=”sql” rownum=”no”]
SQL> create table test_compress (id number, name varchar2(3000)) compress for all operations;
Table created.
SQL> begin
2 for i in 1..10000 loop
3 insert into test_compress values(i,lpad(’test’,3000,’x') );
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL>analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks from dba_tables where table_name=’TEST_COMPRESS’;
TABLE_NAME BLOCKS
———- ———-
TEST_COMPRESS 118
SQL> drop table test_compress;
Table dropped.
SQL> create table test_compress (id number, name varchar2(3000)) compress for all operations;
Table created.
SQL> begin
2 for i in 1..10000 loop
3 insert into test_compress values(i,lpad(’test’,3000,’x') );
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
SQL>commit;
Commit complete
SQL>analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks from dba_tables where table_name=’TEST_COMPRESS’;
TABLE_NAME BLOCKS
———- ———-
TEST_COMPRESS 5032
SQL> alter table test_compress move;
Table altered.
SQL> analyze table test_compress estimate statistics;
Table analyzed.
SQL>select table_name,blocks from dba_tables where table_name=’TEST_COMPRESS’
TABLE_NAME BLOCKS
———- ———-
TEST_COMPRESS 59
[/coolcode]
如上测试，由transaction来触发压缩，并且只针对DML操作touch的block进行压缩。
且频繁的更新操作会导致表上有较多的Chained Rows。
[coolcode lang=”sql” rownum=”no”]
SQL> create index idx2 on test_compress(id);
Index created.
SQL> begin
2 for i in 1..1000 loop
3 update test_compress set id=id+i+40000 where id=i;
4 commit;
5 end loop;
6 end;
7 /
PL/SQL procedure successfully completed.
SQL> analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks from dba_tables where table_name=’TEST_COMPRESS’;
TABLE_NAME BLOCKS
———- ———-
TEST_COMPRESS 59
SQL> begin
2 for i in 1..10000 loop
3 if mod(i,5) = 0 then
4 update test_compress set id=id+i+40000,name=lpad(’update’,3000,’x') where id=i;
5 end if;
6 commit;
7 end loop;
8 end;
9 /
PL/SQL procedure successfully completed.
SQL> analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks,CHAIN_CNT from dba_tables where table_name=’TEST_COMPRESS’;
TABLE_NAME BLOCKS CHAIN_CNT
———- ————– ———-
TEST_COMPRESS 1888 20673
SQL> select count(*) from TEST_COMPRESS;
COUNT(*)
———-
20000
SQL> alter table TEST_COMPRESS move;
Table altered.
SQL> alter index BINZHANG.IDX2 rebuild;
Index altered.
SQL> analyze table test_compress estimate statistics;
Table analyzed.
SQL> select table_name,blocks,CHAIN_CNT from dba_tables where table_name=’TEST_COMPRESS’;
TABLE_NAME BLOCKS CHAIN_CNT
———- ———- ———-
TEST_COMPRESS 62 0
[/coolcode]
该功能的增强使得在CPU不是系统瓶颈的服务器上压缩不常用的相对不重要但要保留的历史纪录的时候多了一些灵活性。
more info see
Oracle 11g Data Compression Tips for the Database Administrator