ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Table Compression

Table Compression

原创 Linux操作系统 作者:jiuniang012 时间:2009-09-05 15:51:00 0 删除 编辑
Table Compression 表压缩:
       当你的数据库大小增长到几百G或T时,可以考虑压缩表. 压缩表节省空间,减少buffer cache缓存池的内存使用. 表压缩还能提高读数据时的查询速度. 但牺牲的是DML,数据装载时CPU的使用率. 在OLAP系统上特点尤为突出,OLTP系统也可以用.

可以在创建表时指定表压缩,也可以把已经建好的表改为表压缩,但只有更改后的数据是压缩的,同样也可以把一个压缩表更改为一个非压缩表,只有在更改后的数据才是非压缩的.

压缩表有两种选项:
COMPRESS FOR DIRECT_LOAD OPERATIONS: 这是默认的压缩选项,即只对直接插入的数据压缩.
COMPRESS FOR ALL OPERATIONS: 对所以的DML操作的数据都压缩,但COMPATIBLE参数必须设为11.1.0或更高.

实例1:
SQL>  CREATE TABLE t1 (a number, b varchar2(10)) COMPRESS FOR ALL OPERATIONS;

Table created.

SQL> CREATE TABLE t2 (a number, b varchar2(10)) COMPRESS FOR DIRECT_LOAD OPERATIONS;

Table created.

SQL> CREATE TABLE t3 (a number, b varchar2(10)) COMPRESS;

Table created.

SQL> CREATE TABLE t4 (a number, b varchar2(10));

Table created.

SQL> SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
T4                             DISABLED
T3                             ENABLED  DIRECT LOAD ONLY
T2                             ENABLED  DIRECT LOAD ONLY
T1                             ENABLED  FOR ALL OPERATIONS

SQL> ALTER TABLE t4 COMPRESS FOR ALL OPERATIONS;

Table altered.

SQL> SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
T4                             ENABLED  FOR ALL OPERATIONS
T3                             ENABLED  DIRECT LOAD ONLY
T2                             ENABLED  DIRECT LOAD ONLY
T1                             ENABLED  FOR ALL OPERATIONS

SQL> ALTER TABLE t4 COMPRESS FOR DIRECT_LOAD OPERATIONS;

Table altered.

SQL> SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
T4                             ENABLED  DIRECT LOAD ONLY
T3                             ENABLED  DIRECT LOAD ONLY
T2                             ENABLED  DIRECT LOAD ONLY
T1                             ENABLED  FOR ALL OPERATIONS

实例2: 分区表压缩
SQL> CREATE TABLE sales(
  2   saleskey number,
  3   quarter number,
  4   product number,
  5   salesperson number,
  6   amount number(12,2),
  7   region varchar2(10)) COMPRESS
  8  PARTITION BY LIST(region)
  9   ( PARTITION northwest VALUES ('NORTHWEST'),
 10     PARTITION southwest VALUES ('SOUTHWEST'),
 11     PARTITION northeast VALUES ('NORTHEAST') NOCOMPRESS,
 12     PARTITION southeast VALUES ('SOUTHEAST'));

Table created.

SQL> SELECT table_name, compression, compress_for FROM user_tables;

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
SALES
T4                             ENABLED  DIRECT LOAD ONLY
T3                             ENABLED  DIRECT LOAD ONLY
T2                             ENABLED  DIRECT LOAD ONLY
T1                             ENABLED  FOR ALL OPERATIONS

注: 分区表中的压缩并不会在USER_TABLES里显示压缩特性, 而是在*_TAB_PARTITIONS表里显示.

SQL> select TABLE_NAME,PARTITION_NAME,PARTITION_POSITION,COMPRESSION,COMPRESS_FOR FROM user_tab_partitions order by 3;

TABLE_NAME                     PARTITION_NAME                 PARTITION_POSITION COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ ------------------ -------- ------------------
SALES                          NORTHWEST                                       1 ENABLED  DIRECT LOAD ONLY
SALES                          SOUTHWEST                                       2 ENABLED  DIRECT LOAD ONLY
SALES                          NORTHEAST                                        3 DISABLED
SALES                          SOUTHEAST                                        4 ENABLED  DIRECT LOAD ONLY

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

请登录后发表评论 登录
全部评论

注册时间:2009-07-02

  • 博文量
    126
  • 访问量
    211238