Oracle压缩表的一点测试

Sql代码  
---压缩表可减少数据量，从而减少IO  
  
DROP TABLE t purge;  
  
CREATE TABLE t NOCOMPRESS AS  
SELECT rownum AS n, rpad(' ',500,mod(rownum,15)) AS pad  
FROM dual  
CONNECT BY level <= 200000;  
  
--收集表统计信息  
  
admin@ORCL> execute dbms_stats.gather_table_stats('ADMIN','T');  
  
PL/SQL 过程已成功完成。

--未压缩的表当前情况  
admin@ORCL> SELECT table_name, blocks,compression  FROM user_tables WHERE table_name = 'T';  
  
TABLE_NAME                         BLOCKS COMPRESS  
------------------------------ ---------- --------  
T                                   14449 DISABLED  
admin@ORCL> set autotrace on  
--查看资源消耗，COST 为3185,逻辑读为14297  
admin@ORCL> select count(*) from t;  
  
  COUNT(*)  
----------  
    200000  
  
  
执行计划  
----------------------------------------------------------  
Plan hash value: 2966233522  
  
-------------------------------------------------------------------  
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |  
-------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |     1 |  3185   (1)| 00:00:39 |  
|   1 |  SORT AGGREGATE    |      |     1 |            |          |  
|   2 |   TABLE ACCESS FULL| T    |   199K|  3185   (1)| 00:00:39 |  
-------------------------------------------------------------------  
  
  
统计信息  
----------------------------------------------------------  
          1  recursive calls  
          0  db block gets  
      14297  consistent gets  
          0  physical reads  
          0  redo size  
        408  bytes sent via SQL*Net to client  
        385  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          1  rows processed  
  
  
--开始压缩表  
  
ALTER TABLE t MOVE COMPRESS;  
  
execute dbms_stats.gather_table_stats('ADMIN','T');  
  
admin@ORCL> SELECT table_name, blocks,compression  FROM user_tables WHERE table_name = 'T';  
  
TABLE_NAME                         BLOCKS COMPRESS  
------------------------------ ---------- --------  
T                                    2639 ENABLED  
  
set autotrace on   
admin@ORCL> select count(*) from t;  
  
  COUNT(*)  
----------  
    200000  
  
  
执行计划  
----------------------------------------------------------  
Plan hash value: 2966233522  
  
-------------------------------------------------------------------  
| Id  | Operation          | Name | Rows  | Cost (%CPU)| Time     |  
-------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |     1 |   587   (2)| 00:00:08 |  
|   1 |  SORT AGGREGATE    |      |     1 |            |          |  
|   2 |   TABLE ACCESS FULL| T    |   200K|   587   (2)| 00:00:08 |  
-------------------------------------------------------------------  
  
  
统计信息  
----------------------------------------------------------  
          0  recursive calls  
          0  db block gets  
       2596  consistent gets  
          0  physical reads  
        116  redo size  
        408  bytes sent via SQL*Net to client  
        385  bytes received via SQL*Net from client  
          2  SQL*Net roundtrips to/from client  
          0  sorts (memory)  
          0  sorts (disk)  
          1  rows processed  
/
注意：压缩后的表更新的开销会更大，查询耗费的CPU也更多。所以压缩表一般适合在更新比较少，且CPU消耗不大，IO消耗很大系统中试用。