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
当你的数据库大小增长到几百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