为什么要这样压缩表？

〇〇

如果第一列不重复,第2列重复也能压缩

SQL> select trunc(100,10) from dual;

TRUNC(100,10)
-------------
          100

已用时间:  00: 00: 00.00
SQL> drop table t purge;

表已删除。

已用时间:  00: 00: 00.11
SQL> create table t pctfree 0 as select * from(select 'A'||trunc(mod(level,100)) a , 'A'||trunc(level/100)b from dual connect by level<=1E6 order by 1);


表已创建。

已用时间:  00: 00: 03.35
SQL> select blocks*8/1024 from user_segments where segment_name='T';

BLOCKS*8/1024
-------------
           15

已用时间:  00: 00: 00.01
SQL> alter table t move compress;

表已更改。

已用时间:  00: 00: 04.02
SQL> select blocks*8/1024 from user_segments where segment_name='T';

BLOCKS*8/1024
-------------
           13

已用时间:  00: 00: 00.01
SQL> drop table t purge;

表已删除。

已用时间:  00: 00: 00.10
SQL> create table t pctfree 0 as select 'A'||trunc(mod(level,100)) a , 'A'||trunc(level/100)b from dual connect by level<=1E6 ;

表已创建。

已用时间:  00: 00: 03.42
SQL> select blocks*8/1024 from user_segments where segment_name='T';

BLOCKS*8/1024
-------------
           15

已用时间:  00: 00: 00.02
SQL> alter table t move compress;

表已更改。

已用时间:  00: 00: 02.62
SQL> select blocks*8/1024 from user_segments where segment_name='T';

BLOCKS*8/1024
-------------
           13

已用时间:  00: 00: 00.01
SQL>

〇〇

PK无法压缩，拆成2部分就可以压缩了
SQL> create table t54 pctfree 0 as select id from hu partition(hu54);

表已创建。

已用时间:  00: 00: 02.87
SQL> select blocks*8/1024 from user_segments where segment_name='T54';

BLOCKS*8/1024
-------------
           13

已用时间:  00: 00: 00.06
SQL> alter table t54 move compress;

表已更改。

已用时间:  00: 00: 02.20
SQL> select blocks*8/1024 from user_segments where segment_name='T54';

BLOCKS*8/1024
-------------
           13

已用时间:  00: 00: 00.01
SQL> create table t54_2 pctfree 0 as select substrb(id,1,6)id0,substr(id,7)id1 from hu partition(hu54);

表已创建。

已用时间:  00: 00: 00.72
SQL> select blocks*8/1024 from user_segments where segment_name='T54';

BLOCKS*8/1024
-------------
           13

已用时间:  00: 00: 00.01
SQL> select sum(bytes) from user_segments where segment_name='T54';

SUM(BYTES)
----------
  13631488

已用时间:  00: 00: 00.05
SQL> select sum(bytes) from user_segments where segment_name='T54_2';

SUM(BYTES)
----------
  14680064

已用时间:  00: 00: 00.05
SQL> alter table t54_2 move compress;

表已更改。

已用时间:  00: 00: 02.15
SQL> select sum(bytes) from user_segments where segment_name='T54_2';

SUM(BYTES)
----------
  11534336

已用时间:  00: 00: 00.01
SQL> select blocks*8/1024 from user_segments where segment_name='T54_2';

BLOCKS*8/1024
-------------
           11

已用时间:  00: 00: 00.05

〇〇

SQL> create table t54_3 pctfree 0 as select substrb(id,1,6)id0,substr(id,7,6)id1,substrb(id,13)id3 from hu partition(hu54)order by 1,2,3;

表已创建。

已用时间:  00: 00: 03.08
SQL> select blocks*8/1024 from user_segments where segment_name='T54_3';

BLOCKS*8/1024
-------------
           15

已用时间:  00: 00: 00.10
SQL> alter table t54_3 move compress;

表已更改。

已用时间:  00: 00: 02.69
SQL> select sum(bytes)s,segment_name from user_segments group by segment_name order by 2;

         S SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
1.6770E+10 HU
1.7807E+10 HUC
5.4105E+10 REN
5.9121E+10 RENC
  13631488 T54
  11534336 T54_2
   8388608 T54_3

已选择7行。

已用时间:  00: 00: 01.88
SQL> select blocks*8/1024 from user_segments where segment_name='T54_3';

BLOCKS*8/1024
-------------
            8

已用时间:  00: 00: 00.01

〇〇

id拆分到一定程度对压缩就没有意义了
SQL> create table t54_7 pctfree 0 as
  2  select substrb(id,1,2)id1,substrb(id,3,2)id2,substrb(id,5,2)id3,
  3  substrb(id,7,3)id4,substrb(id,10,3)id5,
  4  substrb(id,13,3)id6,substrb(id,16,3)id7,
  5  H0,
  6  H02,
  7  H031,
  8  H032,
  9  H041,
10  H042,
11  H051,
12  H052,
13  H061,
14  H062,
15  H071,
16  H072,
17  H081,
18  H082,
19  H09,
20  H10,
21  H11,
22  H12,
23  H13,
24  H14,
25  H15,
26  H16,
27  H17,
28  H18,
29  H19,
30  H20,
31  H21,
32  H22,
33  H23,
34  HA0,
35  HA1,
36  HA2,
37  HA3,
38  HA4,
39  HA5,
40  HA6,
41  HA7,
42  HA8,
43  HA9,
44  HA10,
45  HA11,
46  HA20 from hu partition(hu54) order by 1,2,3,4,5,6,7;

表已创建。

已用时间:  00: 00: 05.18
SQL> create table t54_0 pctfree 0 as select * from hu partition(hu54);

表已创建。

已用时间:  00: 00: 01.49
SQL> select sum(bytes)s,segment_name from user_segments group by segment_name order by 2;

         S SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
1.6770E+10 HU
1.7807E+10 HUC
5.4105E+10 REN
5.9121E+10 RENC
  13631488 T54
  57671680 T54_0
  11534336 T54_2
   8388608 T54_3
  61865984 T54_7

已选择9行。

已用时间:  00: 00: 00.12
SQL> alter table t54_7 move compress;

表已更改。

已用时间:  00: 00: 09.30
SQL> alter table t54_0 move compress;

表已更改。

已用时间:  00: 00: 09.53
SQL> select sum(bytes)s,segment_name from user_segments group by segment_name order by 2;

         S SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
1.6770E+10 HU
1.7807E+10 HUC
5.4105E+10 REN
5.9121E+10 RENC
  13631488 T54
  27262976 T54_0
  11534336 T54_2
   8388608 T54_3
  17825792 T54_7

已选择9行。

已用时间:  00: 00: 00.10
SQL> create table t54_4 pctfree 0 as
  2  select substrb(id,1,6)id1
  3  substrb(id,7,6)id2,
  4  substrb(id,13,3)id6,substrb(id,16,3)id7,
  5  H0,
  6  H02,
  7  H031,
  8  H032,
  9  H041,
10  H042,
11  H051,
12  H052,
13  H061,
14  H062,
15  H071,
16  H072,
17  H081,
18  H082,
19  H09,
20  H10,
21  H11,
22  H12,
23  H13,
24  H14,
25  H15,
26  H16,
27  H17,
28  H18,
29  H19,
30  H20,
31  H21,
32  H22,
33  H23,
34  HA0,
35  HA1,
36  HA2,
37  HA3,
38  HA4,
39  HA5,
40  HA6,
41  HA7,
42  HA8,
43  HA9,
44  HA10,
45  HA11,
46  HA20 from hu partition(hu54) order by 1,2,3,4;
substrb(id,7,6)id2,
*
第 3 行出现错误:
ORA-00923: 未找到要求的 FROM 关键字


已用时间:  00: 00: 00.01
SQL> 2
  2* select substrb(id,1,6)id1
SQL> a ,
  2* select substrb(id,1,6)id1,
SQL> /

表已创建。

已用时间:  00: 00: 02.09
SQL> select sum(bytes)s,segment_name from user_segments group by segment_name order by 2;

         S SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
1.6770E+10 HU
1.7807E+10 HUC
5.4105E+10 REN
5.9121E+10 RENC
  13631488 T54
  27262976 T54_0
  11534336 T54_2
   8388608 T54_3
  59768832 T54_4
  17825792 T54_7

已选择10行。

已用时间:  00: 00: 00.06
SQL> alter table t54_4 move compress;

表已更改。

已用时间:  00: 00: 08.06
SQL> select sum(bytes)s,segment_name from user_segments group by segment_name order by 2;

         S SEGMENT_NAME
---------- ---------------------------------------------------------------------------------
1.6770E+10 HU
1.7807E+10 HUC
5.4105E+10 REN
5.9121E+10 RENC
  13631488 T54
  27262976 T54_0
  11534336 T54_2
   8388608 T54_3
  17825792 T54_4
  17825792 T54_7

已选择10行。

已用时间:  00: 00: 00.05

〇〇

将单列主键改成复合列后的效果
SQL> select sum(bytes)s,segment_name,segment_type from user_segments group by segment_name,segment_type order by 2;

         S SEGMENT_NAME                                                                      SEGMENT_TYPE
---------- --------------------------------------------------------------------------------- ------------------
1.6770E+10 HU                                                                                TABLE PARTITION
1.7807E+10 HUC                                                                               TABLE
5.4105E+10 REN                                                                               TABLE PARTITION
1208614912 REN4                                                                              TABLE PARTITION
1207959552 REN4_1                                                                            TABLE
3.6840E+10 REN5                                                                              TABLE PARTITION
5.9121E+10 RENC                                                                              TABLE
13631488 T54                                                                               TABLE
  27262976 T54_0                                                                             TABLE
  11534336 T54_2                                                                             TABLE
   8388608 T54_3                                                                             TABLE
  17825792 T54_4                                                                             TABLE
  17825792 T54_7                                                                             TABLE

已选择13行。

已用时间:  00: 00: 00.07
SQL>

sundog315

OO，没必要整这么多吧

BTW，当列值很小，比bindmp还小的时候，也是不压缩的。也就是说，当字符型字段值小于5(bindmp最小长度)的时候，是不会进行压缩的。

sundog315

SUNDOG315>select dbms_rowid.rowid_relative_fno(t.rowid) file#,
  2         dbms_rowid.rowid_block_number(t.rowid) block#
  3    from t where t.text>'aaaa' and rownum=1;

     FILE#     BLOCK#
---------- ----------
         4       2006

SUNDOG315>alter system dump datafile 4 block 2006;

系统已更改。

data_block_dump,data header at 0xece227c
===============
tsiz: 0x1f80
hsiz: 0x5c6
pbl: 0x0ece227c
     76543210
flag=--------
ntab=1
nrow=730
frre=-1
fsbo=0x5c6
fseo=0x5d6
avsp=0x10
tosp=0x10
0xe:pti[0]        nrow=730        offs=0
0x12:pri[0]        offs=0x1f78
...
block_row_dump:
tab 0, row 0, @0x1f78
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1

SUNDOG315>select dbms_rowid.rowid_relative_fno(t.rowid) file#,
  2         dbms_rowid.rowid_block_number(t.rowid) block#
  3    from t where t.text>'aaaaa' and rownum=1;

     FILE#     BLOCK#
---------- ----------
         4       2009

SUNDOG315>alter system dump datafile 4 block 2009;

系统已更改。

data_block_dump,data header at 0xece227c
===============
tsiz: 0x1f80
hsiz: 0x5d6
pbl: 0x0ece227c
     76543210
flag=-0------
ntab=2
nrow=730
frre=-1
fsbo=0x5d6
fseo=0x1137
avsp=0x1
tosp=0x1
        r0_9ir2=0x0
        mec_kdbh9ir2=0x0
                      76543210
        shcf_kdbh9ir2=----------
                  76543210
        flag_9ir2=--R----C        Archive compression: N
                fcls_9ir2[2]={ 0 32768 }
0x1a:pti[0]        nrow=2        offs=0
0x1e:pti[1]        nrow=728        offs=2
0x22:pri[0]        offs=0x1f78
0x24:pri[1]        offs=0x1f6f
...
block_row_dump:
tab 0, row 0, @0x1f78
tl: 8 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 5]  61 61 61 61 61
bindmp: 01 e8 cd 61 61 61 61 61
tab 0, row 1, @0x1f6f
tl: 9 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 6]  61 61 61 61 61 61
bindmp: 00 f0 ce 61 61 61 61 61 61
tab 1, row 0, @0x1f6a
tl: 5 fb: --H-FL-- lb: 0x0  cc: 1
col  0: [ 5]  61 61 61 61 61
bindmp: 2c 00 01 01 00

〇〇

原帖由 sundog315 于 2010-9-13 09:10 发表


OO，没必要整这么多吧

BTW，当列值很小，比bindmp还小的时候，也是不压缩的。也就是说，当字符型字段值小于5(bindmp最小长度)的时候，是不会进行压缩的。

我这个没有〉varchar(5)的，但是也压缩了，id是主键，不压缩
CREATE TABLE "RK"."HU1" ("H0" VARCHAR2(1), "ID" VARCHAR2(18), "H02"
VARCHAR2(1), "H031" VARCHAR2(2), "H032" VARCHAR2(2), "H041"
VARCHAR2(2), "H042" VARCHAR2(2), "H051" VARCHAR2(2), "H052"
VARCHAR2(2), "H061" VARCHAR2(2), "H062" VARCHAR2(2), "H071"
VARCHAR2(1), "H072" VARCHAR2(1), "H081" VARCHAR2(1), "H082"
VARCHAR2(1), "H09" VARCHAR2(2), "H10" VARCHAR2(3), "H11" VARCHAR2(1),
"H12" VARCHAR2(1), "H13" VARCHAR2(4), "H14" VARCHAR2(1), "H15"
VARCHAR2(1), "H16" VARCHAR2(1), "H17" VARCHAR2(1), "H18" VARCHAR2(1),
"H19" VARCHAR2(1), "H20" VARCHAR2(1), "H21" VARCHAR2(1), "H22"
VARCHAR2(1), "H23" VARCHAR2(1), "HA0" VARCHAR2(1), "HA1" VARCHAR2(2),
"HA2" VARCHAR2(2), "HA3" VARCHAR2(1), "HA4" VARCHAR2(1), "HA5"
VARCHAR2(1), "HA6" VARCHAR2(1), "HA7" VARCHAR2(1), "HA8" VARCHAR2(1),
"HA9" VARCHAR2(1), "HA10" VARCHAR2(3), "HA11" VARCHAR2(1), "HA20"
VARCHAR2(2))

sundog315

原帖由 〇〇 于 2010-9-13 13:05 发表

我这个没有〉varchar(5)的，但是也压缩了，id是主键，不压缩
CREATE TABLE "RK"."HU1" ("H0" VARCHAR2(1), "ID" VARCHAR2(18), "H02"
VARCHAR2(1), "H031" VARCHAR2(2), "H032" VARCHAR2(2), "H041"
VARCHAR2(2), "H042" VARCHAR2(2), "H051" VARCHAR2(2), "H052"
VARCHAR2(2), "H061" VARCHAR2(2), "H062" VARCHAR2(2), "H071"
VARCHAR2(1), "H072" VARCHAR2(1), "H081" VARCHAR2(1), "H082"
VARCHAR2(1), "H09" VARCHAR2(2), "H10" VARCHAR2(3), "H11" VARCHAR2(1),
"H12" VARCHAR2(1), "H13" VARCHAR2(4), "H14" VARCHAR2(1), "H15"
VARCHAR2(1), "H16" VARCHAR2(1), "H17" VARCHAR2(1), "H18" VARCHAR2(1),
"H19" VARCHAR2(1), "H20" VARCHAR2(1), "H21" VARCHAR2(1), "H22"
VARCHAR2(1), "H23" VARCHAR2(1), "HA0" VARCHAR2(1), "HA1" VARCHAR2(2),
"HA2" VARCHAR2(2), "HA3" VARCHAR2(1), "HA4" VARCHAR2(1), "HA5"
VARCHAR2(1), "HA6" VARCHAR2(1), "HA7" VARCHAR2(1), "HA8" VARCHAR2(1),
"HA9" VARCHAR2(1), "HA10" VARCHAR2(3), "HA11" VARCHAR2(1), "HA20"
VARCHAR2(2))

dump出来看看。空间的减少不一定都是压缩的，有可能是pctfree=0造成的。

〇〇

SQL> create table hupct0 pctfree 0 as select * from hu where rownum<=1E6;

表已创建。

已用时间:  00: 00: 04.80
SQL> create table hucomp compress as select * from hupct0;

表已创建。

已用时间:  00: 00: 24.61
SQL> select sum(bytes)s,segment_name,segment_type from user_segments group by segment_name,segment_type order by 2;

         S SEGMENT_NAME                                                                      SEGMENT_TYPE
---------- --------------------------------------------------------------------------------- ------------------
1.6770E+10 HU                                                                                TABLE PARTITION
1.7807E+10 HUC                                                                               TABLE
  49283072 HUCOMP                                                                            TABLE
109051904 HUPCT0                                                                            TABLE

如果只是主键，是不压缩的

SQL> drop table hupct0 purge;

表已删除。

已用时间:  00: 00: 00.13
SQL> drop table hucomp purge;

表已删除。

已用时间:  00: 00: 00.10
SQL> create table hupct0 pctfree 0 as select id from hu where rownum<=1E6;

表已创建。

已用时间:  00: 00: 03.01
SQL> create table hucomp compress as select * from hupct0;

表已创建。

已用时间:  00: 00: 03.49
SQL> select sum(bytes)s,segment_name,segment_type from user_segments group by segment_name,segment_type order by 2;

         S SEGMENT_NAME                                                                      SEGMENT_TYPE
---------- --------------------------------------------------------------------------------- ------------------
1.6770E+10 HU                                                                                TABLE PARTITION
1.7807E+10 HUC                                                                               TABLE
  25165824 HUCOMP                                                                            TABLE
  25165824 HUPCT0                                                                            TABLE

[ 本帖最后由 〇〇 于 2010-9-13 20:22 编辑 ]