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

sundog315

OLTP占用空间大。

其实很简单，compress for oltp与compress basic不同的地方是：BASIC认为数据不会再被修改，因此，使用了块内所有能够使用的空间来存储数据
avsp=0x3，基本上没有空闲空间，因此，这也就是为什么DML操作后的数据必须做行迁移来存放。
而compress for oltp为了避免行迁移的影响，保留了一部分块内空间，avsp=0x333，以备数据更新的空间。

其实，从本质上来讲，不论BASIC还是FOR OLTP，DML后的新数据都是非压缩存储的。因此，频繁对压缩表进行DML操作会使压缩表逐步的趋向非压缩，失去了优势。

因此，压缩表依然还是适用于out出来的数据，不会被修改（或很少)

〇〇

为什么create table compress bctfree=0
而alter table compress bctfee不变？

SQL> create table tcm (x varchar(20)) compress;

表已创建。

SQL> set long 1000
SQL> select dbms_metadata.get_ddl('TABLE','TCM') from dual;

DBMS_METADATA.GET_DDL('TABLE','TCM')
--------------------------------------------------------------------------------

  CREATE TABLE "LT"."TCM"
   (    "X" VARCHAR2(20)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 0 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC LOGGING
  TABLESPACE "USERS"



SQL> drop table tcm purge;

表已删除。

SQL> create table tcm (x varchar(20));

表已创建。

SQL> select dbms_metadata.get_ddl('TABLE','TCM') from dual;

DBMS_METADATA.GET_DDL('TABLE','TCM')
--------------------------------------------------------------------------------

  CREATE TABLE "LT"."TCM"
   (    "X" VARCHAR2(20)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"



SQL> alter table tcm compress;

表已更改。

SQL> select dbms_metadata.get_ddl('TABLE','TCM') from dual;

DBMS_METADATA.GET_DDL('TABLE','TCM')
--------------------------------------------------------------------------------

  CREATE TABLE "LT"."TCM"
   (    "X" VARCHAR2(20)
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 COMPRESS BASIC LOGGING
  TABLESPACE "USERS"

Yong Huang

原帖由 sundog315 于 2010-4-8 05:31 发表
OLTP占用空间大。

其实很简单，compress for oltp与compress basic不同的地方是：BASIC认为数据不会再被修改，因此，使用了块内所有能够使用的空间来存储数据
avsp=0x3，基本上没有空闲空间，因此，这也就是为什么DML操作后的数据必须做行迁移来存放。
而compress for oltp为了避免行迁移的影响，保留了一部分块内空间，avsp=0x333，以备数据更新的空间。

其实，从本质上来讲，不论BASIC还是FOR OLTP，DML后的新数据都是非压缩存储的。因此，频繁对压缩表进行DML操作会使压缩表逐步的趋向非压缩，失去了优势。

因此，压缩表依然还是适用于out出来的数据，不会被修改（或很少)

That's opposite of what I expected. Looks like the documentation at
http://download.oracle.com/docs/ ... s002.htm#ADMIN11630
is misleading. The last column of Table 19-1 has Yes for both basic and OLTP compression.

For basic compression, footnote2 says "Inserted and updated rows are uncompressed". Well, there're only 3 types of DMLs. Other than insert and update, it's delete. It makes no sense to talk about delete here. So I don't know what this Yes for basic compression under DML column of Table 19-1 means.

The DML "Yes" for OLTP compression doesn't have footnote 2. But according to your test, it's even worse (i.e. less capable of compressing existing data) than basic compression.

Either there's something the documentation doesn't say or we're not reading it correctly.

Just found:
https://support.oracle.com/CSP/m ... roductFamily=Oracle
Bug 9360265: UPDATE STATEMENTS DO NOT COMPRESS DATA WHEN USING COMPRESS FOR OLTP

Workaround is alter table move after update.

Yong Huang

[ 本帖最后由 Yong Huang 于 2010-4-9 10:13 编辑 ]

Yong Huang

原帖由 〇〇 于 2010-4-9 02:18 发表
为什么create table compress bctfree=0
而alter table compress bctfee不变？

Good finding. So we have to remember to alter table pctfree 0 after changing it to compress.

Yong Huang

sundog315

原帖由 Yong Huang 于 2010-4-10 00:13 发表


Good finding. So we have to remember to alter table pctfree 0 after changing it to compress.

Yong Huang

其实在11.2 Basic Compress，只要做过move，这个没有什么必要的。

SQL> create table t pctfree 10 as select * from dba_objects where 1=0;

表已创建。

SQL> insert into t select * from dba_objects;

已创建71889行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
        10         40       1023            0 DISABLED

SQL> alter table t compress;

表已更改。

SQL> alter table t move;

表已更改。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40        295            0 ENABLED  BASIC

[ 本帖最后由 sundog315 于 2010-4-11 20:04 编辑 ]

sundog315

现在发现的最要命的事情是，我没法用直接路径插入来压缩数据：

C:\Users\Administrator>sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on 星期日 4月 11 20:06:44 2010

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

SQL> conn / as sysdba
已连接。
SQL> drop table t;
drop table t
           *
第 1 行出现错误:
ORA-00942: 表或视图不存在


SQL> create table t compress as select * from dba_objects where 1=0;

表已创建。

SQL> insert /*+ append */ into t select * from dba_objects;

已创建71889行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40        919            0 ENABLED  BASIC

SQL> alter table t move;

表已更改。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40        295            0 ENABLED  BASIC

[ 本帖最后由 sundog315 于 2010-4-11 20:17 编辑 ]

sundog315

郁闷了，哪错了？

SQL> create table t compress for oltp as select * from dba_objects where 1=0;

表已创建。

SQL> insert /*+ append */ into t select * from dba_objects;

已创建71889行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
        10         40       1023            0 ENABLED  OLTP

SQL> alter table t move;

表已更改。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
        10         40        329            0 ENABLED  OLTP

sundog315

SQL Loader直接路径加载可以。

SQL> create table t compress as select * from dba_objects where 1=0;

表已创建。

SQL> exec dbms_stats.gather_table_stats('SYS','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40          0            0 ENABLED  BASIC

D:\>sqlldr userid=test/test control=lod.ctl direct=y

SQL*Loader: Release 11.2.0.1.0 - Production on 星期日 4月 11 20:37:03 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.


加载完成 - 逻辑记录计数 5000。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40         19            0 ENABLED  BASIC

SQL> truncate table t;

表被截断。

D:\>sqlldr userid=test/test control=lod.ctl

SQL*Loader: Release 11.2.0.1.0 - Production on 星期日 4月 11 20:37:42 2010

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

达到提交点 - 逻辑记录计数 5000

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0         40         60            0 ENABLED  BASIC

Yong Huang

> 其实在11.2 Basic Compress，只要做过move，这个没有什么必要的。

Of course. But you missed the point. What "alter table compress", followed by "alter table pctfree 0" as he found out, does is to quickly modify the table attributes so future data will be compressed. "Alter table move" is a slow process (unless the segment is small) that compresses existing data.

> 郁闷了，哪错了？

Maybe data in SYS schema or the system tablespace cannot be compressed? Your test is mixed. Why not always use a non-SYS user and a regular tablespace (non-system for sure, and preferably ASSM because that's the default most people use).

Yong Huang

sundog315

原帖由 Yong Huang 于 2010-4-11 22:07 发表
> 其实在11.2 Basic Compress，只要做过move，这个没有什么必要的。

Of course. But you missed the point. What "alter table compress", followed by "alter table pctfree 0" as he found out, does is to quickly modify the table attributes so future data will be compressed. "Alter table move" is a slow process (unless the segment is small) that compresses existing data.

Yong Huang

呵呵，不好意思，没看清题目。
做了一下测试，结果完全符合Yong Huang版主所说，：）：

SQL> create table t as select * from dba_objects;

表已创建。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
        10                  1050            0 DISABLED

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

HEADER_FILE HEADER_BLOCK
----------- ------------
          4          170

SQL> alter table t compress;

表已更改。

SQL> insert /*+ append */ into t select * from dba_objects;

已创建71889行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
        10                  1383            0 ENABLED  BASIC

SQL> conn test/test
已连接。
SQL> alter system dump datafile 4 block 1382;

系统已更改。

Block header dump:  0x01000566
Object id on Block? Y
seg/obj: 0x11eec  csc: 0x00.f9ac4  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000501 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x000a.001.00000242  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000566
data_block_dump,data header at 0x8f2227c
===============
tsiz: 0x1f80
hsiz: 0x328
pbl: 0x08f2227c
     76543210
flag=-0------
ntab=2
nrow=367
frre=-1
fsbo=0x328
fseo=0x689
avsp=0x361                                  --保留了空间
tosp=0x361
        r0_9ir2=0x0
        mec_kdbh9ir2=0x8
                      76543210
        shcf_kdbh9ir2=----------
                  76543210
        flag_9ir2=--R---OC        Archive compression: N
                fcls_9ir2[14]={ 0 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 32768 }
                perm_9ir2[15]={ 11 13 0 14 1 12 8 9 10 2 3 4 5 6 7 }
0x42:pti[0]        nrow=132        offs=0
0x46:pti[1]        nrow=235        offs=132


SQL> alter table t pctfree 0;

表已更改。

SQL>  insert /*+ append */ into t select * from dba_objects;

已创建71891行。

SQL> commit;

提交完成。

SQL> exec dbms_stats.gather_table_stats('TEST','T');

PL/SQL 过程已成功完成。

SQL> select pct_free,pct_used,blocks,empty_blocks,compression,compress_for from
  2  user_tables where table_name='T';

  PCT_FREE   PCT_USED     BLOCKS EMPTY_BLOCKS COMPRESS COMPRESS_FOR
---------- ---------- ---------- ------------ -------- ------------
         0                  1684            0 ENABLED  BASIC

SQL> alter system dump datafile 4 block 1683;

系统已更改。

Block header dump:  0x01000693
Object id on Block? Y
seg/obj: 0x11efa  csc: 0x00.fb271  itc: 3  flg: E  typ: 1 - DATA
     brn: 0  bdba: 0x1000680 ver: 0x01 opc: 0
     inc: 0  exflg: 0

Itl           Xid                  Uba         Flag  Lck        Scn/Fsc
0x01   0x0008.009.000002ef  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x02   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
0x03   0x0000.000.00000000  0x00000000.0000.00  ----    0  fsc 0x0000.00000000
bdba: 0x01000693
data_block_dump,data header at 0xe64827c
===============
tsiz: 0x1f80
hsiz: 0x32e
pbl: 0x0e64827c
     76543210
flag=-0------
ntab=2
nrow=375
frre=-1
fsbo=0x32e
fseo=0x345
avsp=0x17
tosp=0x17
        r0_9ir2=0x0
        mec_kdbh9ir2=0x2a
                      76543210
        shcf_kdbh9ir2=----------
                  76543210
        flag_9ir2=--R---OC        Archive compression: N
                fcls_9ir2[9]={ 0 32768 32768 32768 32768 32768 32768 32768 2 }
                perm_9ir2[15]={ 6 13 0 14 8 9 10 11 12 1 2 5 3 7 4 }
0x38:pti[0]        nrow=130        offs=0
0x3c:pti[1]        nrow=245        offs=130

[ 本帖最后由 sundog315 于 2010-4-12 20:14 编辑 ]