oracle （11gR2）中的表压缩

oracle （11gR2）中的表压缩

压缩的好处

压缩是一项更适合于数据仓库环境的一项oracle 特性。压缩的好处包括以下两个方面：

1、节省存储空间，对应海量的数据来说非常有意义。

2、查询性能将会提高（不是绝对会提高），因为物理I/O 减少，并且提高了内存中数据块的命中率。

不利的方面：

可能增加CPU 的负载。影响DML操作的性能。表中的碎片增多，浪费存储空间。更多详细的说明请参考：

http://www.oracle.com/technetwork/database/options/partitioning/twp-data-compression-10gr2-0505-128172.pdf

造成这些结果，主要是因为误用了表压缩技术，因为表压缩技术是一项更适合于数据仓库环境的 一项oracle 特性。即数据主要是用作查询目的，很少涉及DML操作。

oracle 提供的压缩方式及特性

oracle 提高了4总压缩方式，分别适用不同的场景。分别是：

basic compression：压缩度高，CPU开销最低，适用于DDS。

OLTP compression : 压缩度高，CPU开销最低，适用于OLTP,DDS。

注：压缩可以指定在表空间级，表级，分区级。如果表空间带有压缩属性那么其中的表默认也带有 压缩属性，但是表级的压缩属性可以覆盖表空间级的压缩属性，表的压缩属性与分区的压缩属性的关系 也是这样的。以下主要讲的是表的压缩。

另外有两种压缩方式，不过这两种压缩方式使用范围有限：

SQL> create table object_copy compress for query
  2  as select * from dba_objects;
as select * from dba_objects
                 *
ERROR at line 2:
ORA-64307: hybrid columnar compression is only supported in tablespaces
residing on Exadata storage

也就是说使用混合柱状压缩表空间需要位于exadata一体机上面。更多关于柱状压缩的资料，可以参考：

http://docs.oracle.com/cd/E11882_01/server.112/e17120/tables002.htm

创建表的时候通过指定compress basic 关键字使表使用basic compression的压缩方式。 如果只使用compress 关键字不指定压缩方式，默认是basic方式。使用basic 方式压缩的 特性在于:使用直接路径insert 的记录是压缩的，update 操作相当于解压缩。

SQL> create table objects_1
  2  compress basic
  3  as select object_id,object_name
  4  from dba_objects;
Table created.

SQL> create table objects_1
  2  compress basic
  3  as select object_id,object_name
  4  from dba_objects;
Table created.

指定OLTP方式的压缩方法指定关键字:compress for oltp。OLTP方式的压缩方式特性

在于：使用直接路径insert 的记录依然是压缩的，传统的insert 语句插入的记录也是压缩的， update 语句操作过后数据依然是压缩的。

oracle压缩实战

如何知道一个表是否是压缩的，压缩方式是什么，可以通过以下的查询来获得：

SQL> select table_name,compression,compress_for
  2  from user_tables
  3  where table_name = 'OBJECTS_1';
TABLE_NAME   COMPRESSION      COMPRESS_FOR
------------ ---------------- ------------------------
OBJECTS_1    ENABLED          BASIC

表空间是否是压缩的可以通过查询dba_tablespaces 数据字典。表分区是否是压缩的可以通过 查询[dba|all|user]_tab_partitions 数据字典。

创建如下的分区表，每个分区可以指定不同的压缩方式。

  1  create table part_objects(
  2  object_id number(10),
  3  object_name varchar2(120)
  4  )
  5  partition by range(object_id)(
  6  partition p1 values less than (100000) compress basic,
  7  partition p2 values less than (200000) compress for oltp,
  8  partition p3 values less than (maxvalue) nocompress
  9* )
SQL> /
Table created.

SQL>  select table_name,partition_name,compression,compress_for
  2   from user_tab_partitions
  3   where table_name = 'PART_OBJECTS';
TABLE_NAME   PARTITION_ COMPRESSION      COMPRESS_FOR
------------ ---------- ---------------- ------------------------
PART_OBJECTS P1         ENABLED          BASIC
PART_OBJECTS P2         ENABLED          OLTP
PART_OBJECTS P3         DISABLED

你不仅可以为不同的分区指定不同的压缩方式，你还可以在分区表创建以后改变

这些压缩方式。

1、改变分区对将来插入的数据的压缩方式。

SQL>alter table part_objects modify partition p1 compress for oltp;
Table altered.
SQL>select table_name,partition_name,compression,compress_for
  2    from user_tab_partitions
  3    where table_name = 'PART_OBJECTS';
TABLE_NAME   PARTITION_ COMPRESSION      COMPRESS_FOR
------------ ---------- ---------------- ------------------------
PART_OBJECTS P1         ENABLED          OLTP
PART_OBJECTS P2         ENABLED          OLTP
PART_OBJECTS P3         DISABLED

2、改变分区已经存在和将来插入的数据的压缩方式。

SQL>alter table part_objects move partition p3 compress  basic;
Table altered.
SQL>select table_name,partition_name,compression,compress_for
  2   from user_tab_partitions
  3   where table_name = 'PART_OBJECTS';
TABLE_NAME   PARTITION_ COMPRESSION      COMPRESS_FOR
------------ ---------- ---------------- ------------------------
PART_OBJECTS P1         ENABLED          OLTP
PART_OBJECTS P2         ENABLED          OLTP
PART_OBJECTS P3         ENABLED          BASIC

如果一个表是非压缩的，或者想改变一个表的压缩方式可以通过以下的方式来改变。

SQL> select table_name,compression,compress_for
  2  from user_tables
  3  where table_name = 'EMPLOYEES';
TABLE_NAME   COMPRESSION      COMPRESS_FOR
------------ ---------------- ------------------------
EMPLOYEES    DISABLED

SQL>alter table employees move compress  basic;
Table altered.
SQL>select table_name,compression,compress_for
  2   from user_tables
  3   where table_name = 'EMPLOYEES';
TABLE_NAME   COMPRESSION      COMPRESS_FOR
------------ ---------------- ------------------------
EMPLOYEES    ENABLED          BASIC

压缩表上面存在的限制

1、使用basic compression 方式压缩的表不能删除列。

SQL> alter table employees drop column salary;
alter table employees drop column salary
                                  *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

2、oltp 方式压缩的表，支持删除表中的列，但是oracle内部是把删除的列用unused方式来 处理的 ，从而避免大量的解压缩与压缩操作，不过这导致了不能释放该列占有的存储空间。

SQL> alter table employees move compress for oltp;
Table altered.
SQL> alter table employees drop column salary;
Table altered.

SQL> select * from user_unused_col_tabs
  2  where table_name = 'EMPLOYEES';
TABLE_NAME        COUNT
------------ ----------
EMPLOYEES             1

3、如果你不指定使用basic compression 方式压缩的表的pct_free 参数将会被自动的设置为0。

4、压缩理论上将会增加CPU的负载，但是实际的情况可能不是这样的，因为CPU处理的I/O 操作减少了。 如果I/O 操作不能抵消CPU负载的提高，确保你的系统中有足够CPU资源。

5、压缩表不能超过255个列。

6、压缩表不支持在线段压缩。

7、可以往basic compression 方式的压缩表中添加列，但是不能指定默认值。

SQL> alter table employees move compress basic;
Table altered.
SQL> alter table employees add (salary number(7) default 0);
alter table employees add (salary number(7) default 0)
                           *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables
SQL> alter table employees add (salary number(7));
Table altered.

8、可以往oltp compression 方式的压缩表中添加列，如果指定默认值，该列必须是not null 约束的。

SQL> alter table employees add (income number(7) default 0);
alter table employees add (income number(7) default 0)
                           *
ERROR at line 1:
ORA-39726: unsupported add/drop column operation on compressed tables

SQL> alter table employees add (income number(7) default 0 not null);
Table altered.

9、这里所说的表的压缩方式，不适合与LOBs

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26110315/viewspace-733229/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26110315/viewspace-733229/