关于压缩表的实验

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                                            效果很不错

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21158541/viewspace-668715/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21158541/viewspace-668715/