1、官方文档说法：@H_502_7@
Oracle支持在表空间（tablespace）、数据表（table）和分区（Partition）级别的压缩，如果设置为表空间级别，那么默认将该表空间中的全部的表都进行压缩。
压缩操作可以在数据单条插入、数据修改和数据批量导入时发生。@H_502_7@
@H_502_7@
As your database grows in size,consider using table compression. Compression saves disk space,reduces memory use in the database buffer cache,and can significantly speed query execution during reads. Compression has a cost in cpu overhead for data loading and DML. However,this cost might be offset by reduced I/O requirements@H_502_7@
随着数据库不断增长，可以考虑使用表压缩。压缩可以节省磁盘空间，减少数据库buffer cache内存使用，并且可以加速查询。
压缩对于数据装载和DML操作有一定的cpu消耗。然而，这些消耗可以为I/O的减少而抵消。@H_502_7@
Table compression is completely transparent to applications. It is useful in decision support systems (DSS),online transaction processing (OLTP) systems,and archival systems.@H_502_7@
表压缩对于应用程序完全透明。对于DSS系统、在线事务处理和归档系统都很有用处。@H_502_7@
You can specify compression for a tablespace,a table,or a partition. If specified at the tablespace level,then all tables created in that tablespace are compressed by default.@H_502_7@
你可以为表空间，表或者一个分区指定压缩。如果指定为表空间基本，那么该表空间所有表创建后默认都启用压缩。@H_502_7@
Compression can occur while data is being inserted,updated,or bulk loaded into a table. Operations that permit compression include:
压缩可以再数据插入，更新或者批量装载入表中时发生。压缩表允许以下操作：
Single-row or array inserts and updates 单行或多行插入和更新@H_502_7@
The following direct-path INSERT methods: 直接路径插入方法：@H_502_7@
Direct path sql*Loader
1）CREATE TABLE AS SELECT statements
2）Parallel INSERT statements
3）INSERT statements with an APPEND or APPEND_VALUES hint@H_502_7@
截止目前，Oracle数据库共有4种表压缩技术：
1）Basic compression
2）OLTP compression
3）Warehouse compression (Hybrid Columnar Compression)
4）Archive compression (Hybrid Columnar Compression)@H_502_7@
这里我主要介绍基本压缩：@H_502_7@
2、基本压缩特点：
1）使用基本压缩，只有当数据是直接路径插入或更新记录（direct-path insert and updated ）时才会发生压缩。
并且支持有线的数据类型和sql操作。@H_502_7@
3、如何启用基本压缩？
1）通过create table语句中指定compress条件。
2）通过alter table .. compress; 来给现有表启用压缩；
3）通过alter table .. nocompress; 来禁用表压缩@H_502_7@
4、关于基本压缩的一些例子
4.1 创建压缩表@H_502_7@
CREATE TABLE emp_comp compress
AS
SELECT * FROM emp
WHERE 1=2;
1
2
3
4
4.2 通过数据字典查看压缩表状态@H_502_7@
[email protected]> SELECT table_name,compression,compress_for
2 FROM user_tables
3 WHERE table_name=‘EMP_COMP‘;@H_502_7@
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP ENABLED BASIC
1
2
3
4
5
6
7
4.3 通过非直接路径插入数据@H_502_7@
[email protected]> INSERT INTO emp_comp
2 SELECT * FROM emp;@H_502_7@
已创建16行。@H_502_7@
[email protected]> commit;@H_502_7@
--查看表占用
[email protected]> exec show_space(‘EMP_COMP‘,‘SCOTT‘);
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 0
FS3 Blocks (50-75) .................... 0
FS4 Blocks (75-100) .................... 5
Full Blocks .................... 0
Total Blocks ........................... 8
Total Bytes ........................... 65,536
Total MBytes ........................... 0
Unused Blocks........................... 0
Unused Bytes ........................... 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 14,304
Last Used Block......................... 8@H_502_7@
--看下emp的占用
[email protected]> exec show_space(‘EMP‘,536
Total MBytes ........................... 0
Unused Blocks........................... 0
Unused Bytes ........................... 0
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 144
Last Used Block......................... 8@H_502_7@
--对比与原EMP表的占用情况，emp_comp表并未压缩。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
注：关于show_space过程的用法，请参考【http://blog.csdn.net/indexman/article/details/47207987】@H_502_7@
4.4 通过直接路径插入数据@H_502_7@
drop table emp_comp purge;@H_502_7@
CREATE TABLE emp_comp compress
AS
SELECT * FROM emp
WHERE 1=2;@H_502_7@
insert /*+ append */ into emp_comp
select *
from emp;@H_502_7@
--查看表占用
[email protected]> exec show_space(‘EMP_COMP‘,‘SCOTT‘);
Unformatted Blocks .................... 0
FS1 Blocks (0-25) .................... 0
FS2 Blocks (25-50) .................... 0
FS3 Blocks (50-75) .................... 0
FS4 Blocks (75-100) .................... 0
Full Blocks .................... 1
Total Blocks ........................... 8
Total Bytes ........................... 65,536
Total MBytes ........................... 0
Unused Blocks........................... 4
Unused Bytes ........................... 32,768
Last Used Ext FileId.................... 4
Last Used Ext BlockId................... 14,304
Last Used Block......................... 4@H_502_7@
--很明显少占用4个数据块
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
4.5 禁用表压缩@H_502_7@
[email protected]> alter table emp_comp NOCOMPRESS;@H_502_7@
表已更改。@H_502_7@
[email protected]> SELECT table_name,compress_for
2 FROM user_tables
3 WHERE table_name=‘EMP_COMP‘
4 ;@H_502_7@
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP DISABLED
1
2
3
4
5
6
7
8
9
10
11
12
4.6 启用表压缩@H_502_7@
[email protected]> alter table emp_comp COMPRESS;@H_502_7@
表已更改。@H_502_7@
[email protected]> SELECT table_name,compress_for
2 FROM user_tables
3 WHERE table_name=‘EMP_COMP‘;@H_502_7@
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP ENABLED BASIC
1
2
3
4
5
6
7
8
9
10
11
5、最后来看下表压缩的几个使用限制：
1）对于基本压缩，你无法在压缩表上增加一个带默认值的列：@H_502_7@
[email protected]> alter table emp_comp add remark varchar2(200) default ‘null‘;
alter table emp_comp add remark varchar2(200) default ‘null‘
*
第 1 行出现错误:
ORA-39726: 不支持对压缩表执行添加/删除列操作
1
2
3
4
5
2）无法删除压缩表上的列：@H_502_7@
[email protected]> alter table emp_comp drop column ename;alter table emp_comp drop column ename *第 1 行出现错误:ORA-39726: 不支持对压缩表执行添加/删除列操作123453）表压缩不支持在线段收缩（Online segment shrink） 4）不支持SecureFiles large objects 5）压缩表创建时默认设置PCT_FREE 为 0； 除非你手工指定。@H_502_7@
