Oracle 表压缩(Table Compression)介绍
1、官方文档说法：
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
随着数据库不断增长，可以考虑使用表压缩。压缩可以节省磁盘空间，减少数据库buffer cache内存使用，并且可以加速查询。
压缩对于数据装载和DML操作有一定的CPU消耗。然而，这些消耗可以为I/O的减少而抵消。
Table compression is completely transparent to applications. It is useful in decision support systems (DSS), online transaction processing (OLTP) systems, and archival systems.
表压缩对于应用程序完全透明。对于DSS系统、在线事务处理和归档系统都很有用处。
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.
你可以为表空间，表或者一个分区指定压缩。如果指定为表空间基本，那么该表空间所有表创建后默认都启用压缩。
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 单行或多行插入和更新
The following direct-path INSERT methods: 直接路径插入方法：
Direct path SQL*Loader
1）CREATE TABLE AS SELECT statements
2）Parallel INSERT statements
3）INSERT statements with an APPEND or APPEND_VALUES hint
截止目前，Oracle数据库共有4种表压缩技术：
1）Basic compression
2）OLTP compression
3）Warehouse compression (Hybrid Columnar Compression)
4）Archive compression (Hybrid Columnar Compression)
这里我主要介绍基本压缩：
2、基本压缩特点：
1）使用基本压缩，只有当数据是直接路径插入或更新记录（direct-path insert and updated ）时才会发生压缩。
并且支持有线的数据类型和SQL操作。
3、如何启用基本压缩？
1）通过create table语句中指定compress条件。
2）通过alter table .. compress; 来给现有表启用压缩；
3）通过alter table .. nocompress; 来禁用表压缩
4、关于基本压缩的一些例子
4.1 创建压缩表
CREATE TABLE emp_comp compress
AS
SELECT * FROM emp
WHERE 1=2;1
4.2 通过数据字典查看压缩表状态
SCOTT@orcl> SELECT table_name, compression, compress_for
2 FROM user_tables
3 WHERE table_name='EMP_COMP';
TABLE_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP ENABLED BASIC1
4.3 通过非直接路径插入数据
SCOTT@orcl> INSERT INTO emp_comp
2 SELECT * FROM emp;
已创建16行。
SCOTT@orcl> commit;
--查看表占用
SYS@orcl> 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
--看下emp的占用
SYS@orcl> exec show_space('EMP','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................... 144
Last Used Block......................... 8