从底向上第五篇--了解表的压缩属性

sundog315 · 发表于 2010-4-4 23:07

从底向上第一篇--了解DML操作
从底向上第二篇--了解行迁移
从底向上第三篇--了解index的compress
从底向上第四篇--了解行链接
从底向上第五篇--了解表的压缩属性
从底向上第六篇--compress for oltp真正压缩的阈值触发条件
从底向上第七篇--超255列表的存储
从底向上第八篇--伪造基于ASSM表空间的数据块

在从底向上第三篇--了解index的compress中,我们看到了对于索引而言，使用compress属性是如何改变索引的存储方式的。
这里，对表的compress及compress的适用情况做一下测试。

SQL> conn test/test
已连接。
SQL> create table t compress as select * from dba_tables where 1=0;       --以compress关键字建立一个空表

表已创建。

SQL> insert into t select * from dba_tables;    -- 插入数据

已创建3003行。

SQL> commit;

提交完成。

按照正常逻辑，我们已经指定了compress关键字，那么，表应该是压缩了的。我们看一下。

SQL> select segment_name,blocks,header_file,header_block from dba_segments where
segment_name='T';

SEGMENT_NAME
--------------------------------------------------------------------------------

BLOCKS HEADER_FILE HEADER_BLOCK
---------- ----------- ------------
T
      96          4       794

SQL> alter system dump datafile 4 block 795;

系统已更改。

data_block_dump,data header at 0x20566064
===============
tsiz: 0x1f98
hsiz: 0x5a
pbl: 0x20566064
   76543210
flag=-------- -- 没有压缩块标志
ntab=1 -- 块内只有一个表
nrow=36 -- 这个块放了36行数据
frre=-1
fsbo=0x5a
fseo=0x6d
avsp=0x13
tosp=0x13
0xe:pti[0] nrow=36 offs=0
0x12:pri[0] offs=0x1df4
…
block_row_dump:

SQL> select count(*) from t;

  COUNT(*)
----------
   3002

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time    |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 | 30 (0)| 00:00:01 |
| 1 |  SORT AGGREGATE |    |    1 |          |       |
| 2 | TABLE ACCESS FULL| T |  2977 | 30 (0)| 00:00:01 |
-------------------------------------------------------------------
Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      98  consistent gets
      0  physical reads
      0  redo size
      420  bytes sent via SQL*Net to client
      416  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
1 rows processed

Ok，上面的例子里，简单的插入并不能启动compress，那么应该怎样做，才能使表进行压缩呢？

SQL> conn test/test
已连接。
SQL> truncate table t;

表被截断。

SQL> insert /*+ append */ into t select * from dba_tables; -- 使用直接路径插入

已创建3003行。

SQL> commit;

提交完成。

SQL> select header_file,header_block from dba_segments where segment_name='T';

HEADER_FILE HEADER_BLOCK
----------- ------------
      4       538

SQL> select segment_name,blocks from dba_segments where segment_name='T';

SEGMENT_NAME
--------------------------------------------------------------------------------

BLOCKS
----------
T
      16
对于这个表，压缩比率是多少呢？16/96 大概16.7%的样子。

SQL> alter system dump datafile 4 block 539;

系统已更改。

data_block_dump,data header at 0xd2d827c
===============
tsiz: 0x1f80
hsiz: 0x38e
pbl: 0x0d2d827c
   76543210
flag=-0------ -- 标志O表示这个块是压缩块
ntab=2 -- 块内有两个表，为什么是两个表呢？其实，一个是字典表，一个是数据表（字典的引用）
nrow=384 -- 块内存储了384行数据
frre=-1
fsbo=0x38e
fseo=0x441
avsp=0xb3
tosp=0xb3
-- 下面是压缩块独有的内容：
r0_9ir2=0x0
mec_kdbh9ir2=0x37
               76543210
shcf_kdbh9ir2=----------
            76543210
flag_9ir2=--R---OC
fcls_9ir2[30]={ 0 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 }
perm_9ir2[51]={ 27 50 26 44 0 1 39 42 40 28 43 41 2 3 4 29 30 5 6 48 45 31 32 33 46 34 35 7 8 9 10 49 47 11 12 13 14 15 16 36 37 17 18 19 20 38 21 22 23 24 25 }
0x86:pti[0] nrow=144 offs=0
0x8a:pti[1] nrow=240 offs=144
0x8e:pri[0] offs=0x1b21
…
block_row_dump:
tab 0, row 141, @0x1ea2 -- tab0 字典表
tl: 10 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 7]  78 6e 03 15 12 0f 29
bindmp: 00 02 cf 78 6e 03 15 12 0f 29 -- bindmp，原始数据导出，真正存储的是这个
…
tab 1, row 0, @0x1a50 --tab1 数据表
tl: 22 fb: --H-FL-- lb: 0x0  cc: 51 --压缩后行的大小是22
-- 以下col都是恢复（解压缩）出来的内容
col  0: *NULL*
col  1: [ 5]  56 41 4c 49 44
col  2: [ 2]  c1 02
col  3: [ 6]  c5 16 30 31 25 2e
col  4: *NULL*
col  5: [ 3]  59 45 53
col  6: [ 1]  4e
col  7: [10]  20 20 20 20 20 20 20 20 20 31
col  8: [10]  20 20 20 20 20 20 20 20 20 31
col  9: [ 5]  20 20 20 20 4e
…
col 50: [10]  54 59 50 45 5f 4d 49 53 43 24
-- 以下是行实际存储数据
bindmp: 2c 00 06 1d 03 ca c1 2f 44 6b 6b d2 54 59 50 45 5f 4d 49 53 43 24

SQL> select count(*) from t;

  COUNT(*)
----------
   3002

执行计划
----------------------------------------------------------
Plan hash value: 2966233522

-------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Cost (%CPU)| Time    |
-------------------------------------------------------------------
| 0 | SELECT STATEMENT |    |    1 |    6 (0)| 00:00:01 |
| 1 |  SORT AGGREGATE |    |    1 |          |       |
| 2 | TABLE ACCESS FULL| T |  3002 |    6 (0)| 00:00:01 |
-------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement

统计信息
----------------------------------------------------------
      0  recursive calls
      0  db block gets
      15  consistent gets
      0  physical reads
      0  redo size
      420  bytes sent via SQL*Net to client
      416  bytes received via SQL*Net from client
      2  SQL*Net roundtrips to/from client
      0  sorts (memory)
      0  sorts (disk)
      1  rows processed

由于表数据进行了压缩，数据块大小由96降为16，全表扫描的Cost也由30降到了6,。

Ok，既然数据已经进行了压缩，那么，我们在这个已经压缩的表上再次正常insert数据，看看会不会压缩？

SQL> insert into t select * from dba_tables;

已创建3003行。

SQL> commit;

提交完成。

SQL> select segment_name,blocks from dba_segments where segment_name='T';

SEGMENT_NAME
--------------------------------------------------------------------------------

BLOCKS
----------
T
   112

SQL> alter system dump datafile 4 block 555;

系统已更改。

data_block_dump,data header at 0x2056c064
===============
tsiz: 0x1f98
hsiz: 0x5a
pbl: 0x2056c064
   76543210
flag=-------- -- 不是压缩块
ntab=1
nrow=36
frre=-1
fsbo=0x5a
fseo=0xac
avsp=0x52
tosp=0x52
0xe:pti[0] nrow=36 offs=0
0x12:pri[0] offs=0x1b55
…
block_row_dump:

即使表中已存在的内容是压缩的，后续未采用直接路径insert的数据依然不能压缩。

SQL> conn test/test
已连接。
SQL> drop table t;

表已删除。

SQL> create table t compress as select * from dba_tables;

表已创建。

SQL> select segment_name,blocks from dba_segments where segment_name='T';

SEGMENT_NAME
--------------------------------------------------------------------------------

BLOCKS
----------
T
      16

CTAS可以压缩表数据。

综上，对于压缩表而言，使用范围要比压缩索引小的多，仅仅适用于CTAS或直接路径加载\插入。

并且，一般而言，压缩表由于降低了表的Block数量，直接降低了逻辑读，虽然压缩、解压缩过程会使用额外的CPU资源，但往往是利大于弊。个人认为，对于系统中常用的码表（不容易修改，并且数据量不会太大）压缩可以作为降低资源使用的一种方法。

值得注意的是，对于exp/imp而言，因为无法做直接路径加载，将会导致压缩表在导出、导入后以正常表来进行存储。

关于更深入的了解压缩块的格式，老熊有个很好的文章，请移步http://www.laoxiong.net/dissect_compressed_block_part1.html

[ 本帖最后由 sundog315 于 2010-6-25 14:51 编辑 ]

anlinew · 发表于 2010-4-5 07:30

不常修改的数据采用压缩，那么常修改的数据为何不建议压缩？也就是除了insert以外，对压缩的数据做update是什么情况？

另，业务数据是有生命周期特性的，通常来说一个业务数据表，其中大量数据已经是静态非活跃的了，那么这种情况下，压缩仍然是有好处的，由此又可以进一步考虑，可否结合业务数据的特性利用分区来大概区分静态及动态数据，从而单独对静态数据进行压缩；exchange partition对于压缩表又会是什么情况？。。。。

sundog315 · 发表于 2010-4-5 09:45

如果在已经压缩的数据上进行update的话，oracle会重构一个非压缩的块来存放。如果过多的update操作或后续的非直接路径插入的话，会导致表中的数据越来越倾向于正常表（非压缩）。

对于非活跃的表（历史表，只会被读）采用压缩表可能会利大于弊。

对于exchange partition，貌似是可以的。

SQL> create table t compress as select * from dba_objects;

表已创建。

SQL> create table tt compress partition by range(created) (partition p1 values l
ess than (to_date('20100501','yyyymmdd'))) as select * from t where 1=0;

表已创建。

SQL> alter table tt exchange partition p1 with table t;

表已更改。

SQL> select header_file,header_block from dba_segments where segment_name='TT';

HEADER_FILE HEADER_BLOCK
----------- ------------
      4       442

SQL> alter system dump datafile 4 block 443;

data_block_dump,data header at 0x2099007c
===============
tsiz: 0x1f80
hsiz: 0x2e6
pbl: 0x2099007c
   76543210
flag=-0------
ntab=2
nrow=336
frre=-1
fsbo=0x2e6
fseo=0x2ef
avsp=0x9
tosp=0x9
r0_9ir2=0x0
mec_kdbh9ir2=0x18
               76543210
shcf_kdbh9ir2=----------
            76543210
flag_9ir2=--R---OC
fcls_9ir2[12]={ 0 32768 32768 32768 32768 32768 32768 32768 32768 32768 2 32770 }
perm_9ir2[15]={ 5 13 0 14 12 11 7 10 8 1 3 4 2 9 6 }
0x3e:pti[0] nrow=52 offs=0
0x42:pti[1] nrow=284 offs=52
0x46:pri[0] offs=0x1e39
0x48:pri[1] offs=0x1e14

huanhuanlove · 发表于 2010-4-5 12:01

不错的文章。。。。。。。。顶

Yong Huang · 发表于 2010-4-6 04:41

Different versions of Oracle deal with the same feature differently. It's better to always tell us the version you tested in. In 11g, I think you can have conventional DML, insert or update, on a compressed table and the new data is still compressed, as long as compatible is set to 11g.
http://download.oracle.com/docs/ ... s002.htm#ADMIN11630
calls it "OLTP table compression", which you enable by setting "the COMPATIBLE initialization parameter to 11.1.0 or higher". Can you test it?

Dumping a data block can definitively determine if the block is compressed. On the other hand, if you just want to know if a segment contains any compressed block, you can use the query at
http://download.oracle.com/docs/html/A97349_01/toc.htm

Basically, you check bitand(seg$.spare1, 4096) > 0. According to sql.bsq, that bit is

/* #define KTSSEGM_FLAG_HASCPRSSED 0x1000 */

Yong Huang

sundog315 · 发表于 2010-4-6 18:54

原帖由 Yong Huang 于 2010-4-6 04:41 发表
Different versions of Oracle deal with the same feature differently. It's better to always tell us the version you tested in. In 11g, I think you can have conventional DML, insert or update, on a compressed table and the new data is still compressed, as long as compatible is set to 11g.
http://download.oracle.com/docs/ ... s002.htm#ADMIN11630
calls it "OLTP table compression", which you enable by setting "the COMPATIBLE initialization parameter to 11.1.0 or higher". Can you test it?

Dumping a data block can definitively determine if the block is compressed. On the other hand, if you just want to know if a segment contains any compressed block, you can use the query at
http://download.oracle.com/docs/html/A97349_01/toc.htm

Basically, you check bitand(seg$.spare1, 4096) > 0. According to sql.bsq, that bit is

/* #define KTSSEGM_FLAG_HASCPRSSED 0x1000 */

Yong Huang

多谢，学习了，等下我找个11G来测试一下。

sundog315 · 发表于 2010-4-7 20:16

11.2.0.1测试结果：

11G对于compress 分为标准compress和compress for oltp，两种模式对于DML的操作是不同的

Compress Basic
[oracle@localhost ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 7 08:50:07 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

SQL> conn / as sysdba
Connected.
SQL> drop table t;
drop table t
         *
ERROR at line 1:
ORA-00942: table or view does not exist

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0    Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> create table t compress as select * from dba_objects;

Table created.

SQL> select header_file,header_block from dba_segments where segment_name='T';

HEADER_FILE HEADER_BLOCK
----------- ------------
      1       31088

SQL> alter system dump datafile 1 block 31089;

System altered.

Block header dump:  0x00407971
Object id on Block? Y
seg/obj: 0x34bf  csc: 0x00.2f5bc  itc: 3  flg: -  typ: 1 - DATA
   fsl: 0  fnx: 0x0 ver: 0x01

Itl          Xid                Uba       Flag  Lck       Scn/Fsc
0x01 0xffff.000.00000000  0x00000000.0000.00  C--- 0  scn 0x0000.0002f5bc
0x02 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x03 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
bdba: 0x00407971
data_block_dump,data header at 0xb7fbb674
===============
tsiz: 0x1f88
hsiz: 0x2f4
pbl: 0xb7fbb674
   76543210
flag=-0------
ntab=2
nrow=346
frre=-1
fsbo=0x2f4
fseo=0x2f7
avsp=0x3
tosp=0x3
r0_9ir2=0x0
mec_kdbh9ir2=0x24
               76543210
shcf_kdbh9ir2=----------
            76543210
flag_9ir2=0-R---OC Archive compression: N          --11.2新增，估计与Exadata有关
fcls_9ir2[9]={ 0 32768 32768 32768 32768 32768 32768 32768 2 }
perm_9ir2[15]={ 6 13 0 14 12 8 9 11 10 1 4 5 2 7 3 } --最后一列存放Object_ID

tab 1, row 0, @0x1c60
tl: 15 fb: --H-FL-- lb: 0x0  cc: 15
col  0: *NULL*
col  1: [ 5]  56 41 4c 49 44
col  2: [ 1]  4e
col  3: *NULL*
col  4: [ 1]  4e
col  5: [ 1]  4e
col  6: [ 3]  53 59 53
col  7: [ 2]  c1 02
col  8: [ 5]  54 41 42 4c 45
col  9: [ 7]  78 6e 03 13 0b 0a 12
col 10: [19]  32 30 31 30 2d 30 33 2d 31 39 3a 31 30 3a 30 39 3a 31 37
col 11: [ 7]  78 6e 03 13 0b 0a 12
col 12: [ 2]  c1 03
col 13: [ 5]  49 43 4f 4c 24
col 14: [ 2]  c1 15                               --object_id=20
bindmp: 2c 00 04 08 03 24 cd 49 43 4f 4c 24 ca c1 15
SQL> conn / as sysdba
Connected.

SQL> update t set STATUS='TEST' where object_id=20;

1 row updated.

SQL> select status from t where object_id=20;

STATUS
-------
TEST

SQL> alter system dump datafile 1 block 31089;

System altered.

tab 1, row 0, @0x1c60
tl: 9 fb: --H----- lb: 0x3  cc: 0
nrid:  0x00407e2d.1                      --迁移
bindmp: 20 03 00 00 40 7e 2d 00 01       -- 迁移地址，非压缩

SQL> alter system dump datafile 1 block 32301;

System altered.

Block header dump:  0x00407e2d
Object id on Block? Y
seg/obj: 0x34bf  csc: 0x00.2f7a3  itc: 4  flg: O  typ: 1 - DATA
   fsl: 0  fnx: 0x407e2e ver: 0x01

Itl          Xid                Uba       Flag  Lck       Scn/Fsc
0x01 0x0006.00c.000000bf  0x00c00209.0024.08  C--- 0  scn 0x0000.0002f6ed
0x02 0x0005.011.000000c1  0x00c000c6.0021.26  --U- 1  fsc 0x0000.0002f7ae
0x03 0x0000.000.00000000  0x00000000.0000.00  C--- 0  scn 0x0000.00000000
0x04 0x0000.000.00000000  0x00000000.0000.00  C--- 0  scn 0x0000.00000000
bdba: 0x00407e2d
data_block_dump,data header at 0xb7fbb68c
===============
tsiz: 0x1f70
hsiz: 0x16
pbl: 0xb7fbb68c
   76543210
flag=--------                               --新块，非压缩
ntab=1
nrow=2
frre=-1
fsbo=0x16
fseo=0x1ec9
avsp=0x1eb3
tosp=0x1eb3
0xe:pti[0] nrow=2 offs=0
0x12:pri[0] offs=0x1f1b
0x14:pri[1] offs=0x1ec9
block_row_dump:
tab 0, row 1, @0x1ec9
tl: 82 fb: ----FL-- lb: 0x2  cc: 14
hrid: 0x00407971.0
col  0: [ 3]  53 59 53
col  1: [ 5]  49 43 4f 4c 24
col  2: *NULL*
col  3: [ 2]  c1 15
col  4: [ 2]  c1 03
col  5: [ 5]  54 41 42 4c 45
col  6: [ 7]  78 6e 03 13 0b 0a 12
col  7: [ 7]  78 6e 03 13 0b 0a 12
col  8: [19]  32 30 31 30 2d 30 33 2d 31 39 3a 31 30 3a 30 39 3a 31 37
col  9: [ 4]  54 45 53 54
col 10: [ 1]  4e
col 11: [ 1]  4e
col 12: [ 1]  4e
col 13: [ 2]  c1 02

sundog315 · 发表于 2010-4-7 20:33

Compress for oltp

SQL> conn / as sysdba
Connected.
SQL> drop table t;

Table dropped.

SQL> create table t compress for oltp as select * from dba_objects;

Table created.

SQL> select header_file,header_block from dba_segments where segment_name='T';

HEADER_FILE HEADER_BLOCK
----------- ------------
      1       31088

SQL> alter system dump datafile 1 block 31089;

System altered.

Block header dump:  0x00407971
Object id on Block? Y
seg/obj: 0x34c1  csc: 0x00.2fd3f  itc: 3  flg: -  typ: 1 - DATA
   fsl: 0  fnx: 0x0 ver: 0x01

Itl          Xid                Uba       Flag  Lck       Scn/Fsc
0x01 0xffff.000.00000000  0x00000000.0000.00  C--- 0  scn 0x0000.0002fd3f
0x02 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
0x03 0x0000.000.00000000  0x00000000.0000.00  ---- 0  fsc 0x0000.00000000
bdba: 0x00407971
data_block_dump,data header at 0xb7d53274
===============
tsiz: 0x1f88
hsiz: 0x2a6
pbl: 0xb7d53274
   76543210
flag=-0------
ntab=2
nrow=309
frre=-1
fsbo=0x2a6
fseo=0x5d9
avsp=0x333
tosp=0x333
r0_9ir2=0x0
mec_kdbh9ir2=0x1d
               76543210
shcf_kdbh9ir2=----------
            76543210
flag_9ir2=0-R---OC Archive compression: N
fcls_9ir2[7]={ 0 32768 32768 32768 32768 1 32769 }
perm_9ir2[15]={ 6 12 0 13 14 8 9 11 10 1 4 5 2 7 3 }

tab 1, row 0, @0x1ce5
tl: 15 fb: --H-FL-- lb: 0x0  cc: 15
col  0: *NULL*
col  1: [ 5]  56 41 4c 49 44
col  2: [ 1]  4e
col  3: *NULL*
col  4: [ 1]  4e
col  5: [ 1]  4e
col  6: [ 3]  53 59 53
col  7: [ 2]  c1 02
col  8: [ 5]  54 41 42 4c 45
col  9: [ 7]  78 6e 03 13 0b 0a 12
col 10: [19]  32 30 31 30 2d 30 33 2d 31 39 3a 31 30 3a 30 39 3a 31 37
col 11: [ 7]  78 6e 03 13 0b 0a 12
col 12: [ 5]  49 43 4f 4c 24
col 13: [ 2]  c1 15
col 14: [ 2]  c1 03
bindmp: 2c 00 04 06 03 cd 49 43 4f 4c 24 ca c1 15 1d

SQL> update t set status='TEST' where object_id=20;

1 row updated.

SQL> commit;

Commit complete.

SQL> alter system dump datafile 1 block 31089;

System altered.

虽然块还是压缩的，但这条记录已经是非压缩存储了。
Oracle会在这个块内分配空间，进行存储，而不是迁移到其他块。这是与标准compress的区别所在

B7FBBC00 C9010F02 534554CC FF4EC954 4EC94EC9  [.....TEST.N..N.N]
B7FBBC10 535953CB CD02C1CA 4C424154 6E78CF45  [.SYS....TABLE.xn]
B7FBBC20 0A0B1303 3032DB12 302D3031 39312D33  [......2010-03-19]
B7FBBC30 3A30313A 313A3930 6E78CF37 0A0B1303  [:10:09:17.xn....]
B7FBBC40 4349CD12 CA244C4F C1CA15C1 04002C03  [..ICOL$......,..]

tab 1, row 0, @0x58b
tl: 78 fb: --H-FL-- lb: 0x2  cc: 15
col  0: *NULL*
col  1: [ 4]  54 45 53 54
col  2: [ 1]  4e
col  3: *NULL*
col  4: [ 1]  4e
col  5: [ 1]  4e
col  6: [ 3]  53 59 53
col  7: [ 2]  c1 02
col  8: [ 5]  54 41 42 4c 45
col  9: [ 7]  78 6e 03 13 0b 0a 12
col 10: [19]  32 30 31 30 2d 30 33 2d 31 39 3a 31 30 3a 30 39 3a 31 37
col 11: [ 7]  78 6e 03 13 0b 0a 12
col 12: [ 5]  49 43 4f 4c 24
col 13: [ 2]  c1 15
col 14: [ 2]  c1 03
bindmp: 2c 02 0f 01 c9 cc 54 45 53 54 c9 4e ff c9 4e c9 4e cb 53 59 53 ca c1 02 cd 54 41 42 4c 45 cf 78 6e 03 13 0b 0a 12 db 32 30 31 30 2d 30 33 2d 31 39 3a 31 30 3a 30 39 3a 31 37 cf 78 6e 03 13 0b 0a 12 cd 49 43 4f 4c 24 ca c1 15 ca c1 03

sundog315 · 发表于 2010-4-7 20:37

compress for oltp仅在11.2以上版本支持。

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0    Production
TNS for 32-bit Windows: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production

SQL> create table t compress for oltp as select * from dba_objects;
create table t compress for oltp as select * from dba_objects
                        *
第 1 行出现错误:
ORA-14464: 未指定压缩类型

SQL> create table t compress for query as select * from dba_objects;
create table t compress for query as select * from dba_objects
                        *
第 1 行出现错误:
ORA-14464: 未指定压缩类型

SQL> create table t compress for archive as select * from dba_objects;
create table t compress for archive as select * from dba_objects
                        *
第 1 行出现错误:
ORA-14464: 未指定压缩类型

Yong Huang · 发表于 2010-4-8 02:24

Excellent test. Does standard compression use more space than OLTP compression in 11gR2?

Yong Huang

[笔记] 从底向上第五篇--了解表的压缩属性