1、普通的压缩表

SQL> create table uncompressed pctfree 0 as select * from dba_objects order by owner, object_type, object_name;

Table created.

SQL> create table compressed COMPRESS as select * from uncompressed order by owner, object_type, object_name;

Table created.

SQL> analyze table compressed compute statistics for table;

Table analyzed.

SQL> analyze table uncompressed compute statistics for table;

Table analyzed.

SQL> select cblks comp_blks, uncblks uncomp_blks,round(cblks/uncblks*100,2) pct
from (select max(decode(table_name,'COMPRESSED',blocks,null)) cblks,
max(decode(table_name,'UNCOMPRESSED',blocks,null)) uncblks
from user_tables where table_name in ( 'COMPRESSED', 'UNCOMPRESSED' )
  5  );

 COMP_BLKS UNCOMP_BLKS        PCT
---------- ----------- ----------
       328         618      53.07                                                              看来效果一般

2 随机压缩表

SQL> drop table compressed;

Table dropped.

SQL> create table compressed COMPRESS as select * from uncompressed order by dbms_random.random;

Table created.

SQL> analyze table compressed compute statistics for table;

Table analyzed.

SQL> select cblks comp_blks, uncblks uncomp_blks,round(cblks/uncblks*100,2) pct
from (select max(decode(table_name,'COMPRESSED',blocks,null)) cblks,
max(decode(table_name,'UNCOMPRESSED',blocks,null)) uncblks
from user_tables where table_name in ( 'COMPRESSED', 'UNCOMPRESSED' )
  5  );

 COMP_BLKS UNCOMP_BLKS        PCT
---------- ----------- ----------
       444         618      71.84                                             效果比较差

3、基于时间戳的压缩表

SQL> drop table compressed;

Table dropped.

SQL> create table compressed COMPRESS as select * from uncompressed order by timestamp;

Table created.

SQL> analyze table compressed compute statistics for table;

Table analyzed.

SQL> select cblks comp_blks, uncblks uncomp_blks,round(cblks/uncblks*100,2) pct
from (select max(decode(table_name,'COMPRESSED',blocks,null)) cblks,
max(decode(table_name,'UNCOMPRESSED',blocks,null)) uncblks
from user_tables where table_name in ( 'COMPRESSED', 'UNCOMPRESSED' )
  5  );

 COMP_BLKS UNCOMP_BLKS        PCT
---------- ----------- ----------
       218         618      35.28                                            效果很不错