11gR1 11gR2 compress 与compress for oltp

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

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

点击(此处)折叠或打开

1. 11gR1,不支持compress for oltp,
   
2. SQL> create table xjmon.test nologging as select * from dba_tables ;
   
3. 
   
4. Table created.
   
5. 
   
6. SQL> select
   
7.   2 c.COMPRESSION,c.COMPRESS_FOR
   
8.   3 from dba_tables c
   
9.   4 where c.TABLE_NAME = 'TEST'
   
10.   5 and c.OWNER = 'XJMON';
    
11. 
    
12. COMPRESS COMPRESS_FOR
    
13. -------- ------------------
    
14. DISABLED
    
15. 
    
16. SQL>
    
17. SQL> ALTER table xjmon.test move compress nologging;
    
18. 
    
19. Table altered.
    
20. 
    
21. SQL> select
    
22.   2 c.COMPRESSION,c.COMPRESS_FOR
    
23.   3 from dba_tables c
    
24.   4 where c.TABLE_NAME = 'TEST'
    
25.   5 and c.OWNER = 'XJMON';
    
26. 
    
27. COMPRESS COMPRESS_FOR
    
28. -------- ------------------
    
29. ENABLED DIRECT LOAD ONLY
    
30. 
    
31. SQL>
    
32. SQL> ALTER table xjmon.test move nocompress nologging;
    
33. 
    
34. Table altered.
35. 
    
36. SQL> ALTER table xjmon.test move compress for oltp nologging;
    
37. ALTER table xjmon.test move compress for oltp nologging
    
38.                                          *
    
39. ERROR at line 1:
    
40. ORA-14464: Compression Type not specified

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

点击(此处)折叠或打开

1. SQL> create table xjmon.test nologging as select * from dba_tables;
   
2. 
   
3. Table created.
   
4. 
   
5. Elapsed: 00:00:01.02
   
6. SQL> select
   
7.   2 c.COMPRESSION,c.COMPRESS_FOR
   
8.   3 from dba_tables c
   
9.   4 where c.TABLE_NAME = 'TEST'
   
10.   5 and c.OWNER = 'XJMON';
    
11. 
    
12. COMPRESS COMPRESS_FOR
    
13. -------- ------------
    
14. DISABLED
    
15. 
    
16. Elapsed: 00:00:00.01
    
17. SQL>
    
18. SQL> alter table xjmon.test move compress nologging ;
    
19. 
    
20. Table altered.
    
21. 
    
22. Elapsed: 00:00:00.32
    
23. SQL> select
    
24.   2 c.COMPRESSION,c.COMPRESS_FOR
    
25.   3 from dba_tables c
    
26.   4 where c.TABLE_NAME = 'TEST'
    
27.   5 and c.OWNER = 'XJMON';
    
28. 
    
29. COMPRESS COMPRESS_FOR
    
30. -------- ------------
    
31. ENABLED BASIC
    
32. 
    
33. Elapsed: 00:00:00.01
    
34. SQL>
    
35. SQL> alter table xjmon.test move compress for oltp nologging ;
    
36. 
    
37. Table altered.
    
38. 
    
39. Elapsed: 00:00:00.23
    
40. SQL> select
    
41.   2 c.COMPRESSION,c.COMPRESS_FOR
    
42.   3 from dba_tables c
    
43.   4 where c.TABLE_NAME = 'TEST'
    
44.   5 and c.OWNER = 'XJMON';
    
45. 
    
46. COMPRESS COMPRESS_FOR
    
47. -------- ------------
    
48. ENABLED OLTP
    
49. 
    
50. Elapsed: 00:00:00.00

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

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

转载于:http://blog.itpub.net/28551528/viewspace-2122899/