话说comoress for oltp 在11g才支持，在不同版本之间做表压缩时，发现oltp 在11gR2版本才可以正常执行。
在实际测试过程中，发现compress 在11gR1 和11gR2不同小版本之间还存在一些差异

11gR1 在compress后，COMPRESS_FOR属性为DIRECT LOAD ONLY

点击(此处)折叠或打开

11gR1,不支持compress for oltp,
SQL> create table xjmon.test nologging as select * from dba_tables ;
Table created.
SQL> select
2 c.COMPRESSION,c.COMPRESS_FOR
3 from dba_tables c
4 where c.TABLE_NAME = 'TEST'
5 and c.OWNER = 'XJMON';
COMPRESS COMPRESS_FOR
-------- ------------------
DISABLED
SQL>
SQL> ALTER table xjmon.test move compress nologging;
Table altered.
SQL> select
2 c.COMPRESSION,c.COMPRESS_FOR
3 from dba_tables c
4 where c.TABLE_NAME = 'TEST'
5 and c.OWNER = 'XJMON';
COMPRESS COMPRESS_FOR
-------- ------------------
ENABLED DIRECT LOAD ONLY
SQL>
SQL> ALTER table xjmon.test move nocompress nologging;
Table altered.
SQL> ALTER table xjmon.test move compress for oltp nologging;
ALTER table xjmon.test move compress for oltp nologging
*
ERROR at line 1:
ORA-14464: Compression Type not specified

11gR2 在compress后，COMPRESS_FOR属性为BASIC，并且支持compress for oltp

点击(此处)折叠或打开

SQL> create table xjmon.test nologging as select * from dba_tables;
Table created.
Elapsed: 00:00:01.02
SQL> select
2 c.COMPRESSION,c.COMPRESS_FOR
3 from dba_tables c
4 where c.TABLE_NAME = 'TEST'
5 and c.OWNER = 'XJMON';
COMPRESS COMPRESS_FOR
-------- ------------
DISABLED
Elapsed: 00:00:00.01
SQL>
SQL> alter table xjmon.test move compress nologging ;
Table altered.
Elapsed: 00:00:00.32
SQL> select
2 c.COMPRESSION,c.COMPRESS_FOR
3 from dba_tables c
4 where c.TABLE_NAME = 'TEST'
5 and c.OWNER = 'XJMON';
COMPRESS COMPRESS_FOR
-------- ------------
ENABLED BASIC
Elapsed: 00:00:00.01
SQL>
SQL> alter table xjmon.test move compress for oltp nologging ;
Table altered.
Elapsed: 00:00:00.23
SQL> select
2 c.COMPRESSION,c.COMPRESS_FOR
3 from dba_tables c
4 where c.TABLE_NAME = 'TEST'
5 and c.OWNER = 'XJMON';
COMPRESS COMPRESS_FOR
-------- ------------
ENABLED OLTP
Elapsed: 00:00:00.00

接下来，会实际测试oltp和basic 两种不同的压缩方式insert 及insert append 方式的压缩比例及插入效率的影响。

ITPub博客

11gR1 11gR2 compress 与compress for oltp

最新文章