Table Compression

来源：互联网发布：交易平台php源码编辑：程序博客网时间：2024/05/17 23:52

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

0 0