---压缩表可减少数据量,从而减少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消耗很大系统中试用。