Oracle 表压缩(Table Compression)技术介绍

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  BASIC

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

--对比与原EMP表的占用情况，emp_comp表并未压缩。

注：关于show_space过程的用法，请参考【http://blog.csdn.net/indexman/article/details/47207987】

4.4 通过直接路径插入数据

drop table emp_comp purge;

CREATE TABLE emp_comp compress
AS
SELECT * FROM emp
WHERE 1=2;


insert /*+ append */ into emp_comp
select *
from emp;

--查看表占用
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) ....................               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

--很明显少占用4个数据块

4.5 禁用表压缩

SCOTT@orcl> alter table emp_comp NOCOMPRESS;

表已更改。

SCOTT@orcl> SELECT table_name, compression, compress_for
  2  FROM user_tables
  3  WHERE table_name=‘EMP_COMP‘
  4  ;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP_COMP                       DISABLED

4.6 启用表压缩

SCOTT@orcl> alter table emp_comp COMPRESS;

表已更改。

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  BASIC

5、最后来看下表压缩的几个使用限制：
1）对于基本压缩，你无法在压缩表上增加一个带默认值的列：

SCOTT@orcl> alter table emp_comp add remark varchar2(200) default ‘null‘;
alter table emp_comp add remark varchar2(200) default ‘null‘
                         *
第 1 行出现错误:
ORA-39726: 不支持对压缩表执行添加/删除列操作

2）无法删除压缩表上的列：

SCOTT@orcl> alter table emp_comp drop column ename;
alter table emp_comp drop column ename
                                 *
第 1 行出现错误:
ORA-39726: 不支持对压缩表执行添加/删除列操作

3）表压缩不支持在线段收缩（Online segment shrink）
4）不支持SecureFiles large objects
5）压缩表创建时默认设置PCT_FREE 为 0； 除非你手工指定。

版权声明：本文为博主原创文章，未经博主允许不得转载。

Oracle 表压缩(Table Compression)技术介绍

标签：表压缩   compress