oracle压缩和mysql压缩_oracle 数据压缩特性实际应用及优缺点汇总

九、数据压缩和DML操作

答：不管从实际应用还是官方说明，“压缩”技术都是属于OLAP数据仓库领域

问：那么在OLTP领域就不能使用“压缩”技术了嘛？ 其实不然，特定场合DML操作可以压缩处理的，但大部分是不作压缩的

特定场合

(1)alter table 表名 move compress           给普通表进行压缩，通过MOVE方式进行数据重组

(2)alter table 表名 move partition 分区名 compress  给分区表进行压缩，每个分区都可以独立压缩，结尾必须添加compress关键字否则不做压缩

(3)create table 表名 compress as select     CTAS  对象属性

(4)insert /*+ append */                    直接加载插入数据，必压缩。 常规 insert into 不压缩数据

(5)sqlldr 参数 direct path                  sqlload 直接路径加载，必压缩

实验 1   CTAS

LS@LEO> create table leo_test1 compress as select * from dba_objects;                CTAS 创建初就压缩

Table created.

LS@LEO> create table leo_test2 compress as select * from dba_objects where 1=2;      只要结构不要数据

Table created.

LS@LEO> select count(*) from leo_test1;                                              有10378行

COUNT(*)

----------

10378

LS@LEO> select count(*) from leo_test2;                                              只要结构不要数据 successful

COUNT(*)

----------

0

LS@LEO> insert into leo_test2 select * from dba_objects;                             常规insert  into  不压缩数据

10379 rows created.

LS@LEO> commit;                                                                      提交

LS@LEO> set serveroutput on;                                                         打开屏幕输出

LS@LEO> execute show_space('LEO_TEST1');                                            使用CTAS直接压缩

Unformatted Blocks .....................               0

FS1 Blocks (0-25)  .....................                 0

FS2 Blocks (25-50) .....................                0

FS3 Blocks (50-75) .....................                0

FS4 Blocks (75-100).....................               0

Full Blocks        .....................                       45                            其中用了45个装满数据

Total Blocks............................                     56                            占用了56个数据块

Total Bytes.............................                     458,752                            0.43M空间，已压缩

Total MBytes............................                   0

Unused Blocks...........................                  5

Unused Bytes............................                  40,960

Last Used Ext FileId....................                  7

Last Used Ext BlockId...................                9,649

Last Used Block.........................                   3

PL/SQL procedure successfully completed.

LS@LEO> execute show_space('LEO_TEST2');                                           常规insert into 不压缩数据

Unformatted Blocks .....................               0

FS1 Blocks (0-25)  .....................                 1

FS2 Blocks (25-50) .....................                0

FS3 Blocks (50-75) .....................                0

FS4 Blocks (75-100).....................               2

Full Blocks        .....................                      115                            其中用了115个装满数据

Total Blocks............................                    128                            占用了128个数据块

Total Bytes.............................                    1,048,576

Total MBytes............................                  1                            1M空间

Unused Blocks...........................                 0

Unused Bytes............................                 0

Last Used Ext FileId....................                 7

Last Used Ext BlockId...................              9,777

Last Used Block.........................                 8

PL/SQL procedure successfully completed.

小结：我们看到leo_test2表比leo_test1表占用的空间几乎多了一倍，为什么都用CTAS创建的表空间会不一样呢，leo_test2表在创建时只有结构没有数据，压缩只针对数据压缩因此没有数据就没有压缩。后来我们使用了常规insert into 插入数据但数据是不压缩的，这是导致表2比表1占用空间多的原因。

实验 2   insert /*+ append */               直接加载插入数据，必压缩

LS@LEO> truncate table leo_test2;                                                   清空leo_test2表

Table truncated.

LS@LEO> insert /*+ append */ into leo_test2 select * from dba_objects;              直接加载插入数据必压缩

10379 rows created.

LS@LEO> commit;                                                                     提交

LS@LEO> execute show_space('LEO_TEST2');                                            分析空间使用情况

Unformatted Blocks .....................               0

FS1 Blocks (0-25)  .....................                 0

FS2 Blocks (25-50) .....................                0

FS3 Blocks (50-75) .....................                0

FS4 Blocks (75-100).....................               0

Full Blocks        .....................                      45                            其中用了45个装满数据

Total Blocks............................                    56                            占用了56个数据块

Total Bytes.............................                    458,752                            0.43M空间，已压缩

Total MBytes............................                  0

Unused Blocks...........................                5

Unused Bytes............................                40,960

Last Used Ext FileId....................               7

Last Used Ext BlockId...................            9,705

Last Used Block.........................               3

小结：使用insert /*+ append */ 直接加载插入数据方式，数据必须压缩状态

实验 3   update操作对压缩数据作解压缩处理，空间立刻恢复为未压缩时的容量

LS@LEO> update leo_test1 set object_name=object_name||'leo_nocompress';         leo_test1已经是压缩状态，现在对其进行update操作

10378 rows updated.

LS@LEO> commit;

Commit complete.

LS@LEO> execute show_space('LEO_TEST1');                                        update操作对压缩数据进行解压缩处理

Unformatted Blocks .....................               0

FS1 Blocks (0-25)  .....................                 0

FS2 Blocks (25-50) .....................                1

FS3 Blocks (50-75) .....................                0

FS4 Blocks (75-100).....................              36

Full Blocks        .....................                      143                        其中用了143个装满数据

Total Blocks............................                    256                        占用了256个数据块

Total Bytes.............................                    2,097,152

Total MBytes............................                  2                        2M空间，已经解压缩了，空间恢复为未压缩是的容量

Unused Blocks...........................                 64

Unused Bytes............................                 524,288

Last Used Ext FileId....................                 7

Last Used Ext BlockId...................              9,865

Last Used Block.........................                 64

PL/SQL procedure successfully completed.

小结：此时leo_test1表的空间立刻恢复为未压缩时的容量，但比原来的空间还要大呢，是因为update操作将记录的长度变大了，空间自然也变大了！

实验 4   给普通表进行压缩，通过MOVE方式进行数据重组

LS@LEO> alter table leo_test1 move compress;                                   现在对普通表进行压缩重组数据

Table altered.

LS@LEO> execute show_space('LEO_TEST1');

Unformatted Blocks .....................               0

FS1 Blocks (0-25)  .....................                 0

FS2 Blocks (25-50) .....................                0

FS3 Blocks (50-75) .....................                0

FS4 Blocks (75-100).....................               0

Full Blocks        .....................                       65                       其中用了65个装满数据

Total Blocks............................                     72                       占用了72个数据块

Total Bytes.............................                     589,824                       0.56M空间，已经压缩

Total MBytes............................                   0

Unused Blocks...........................                 0

Unused Bytes............................                 0

Last Used Ext FileId....................                 7

Last Used Ext BlockId...................              9,849

Last Used Block.........................                 8

PL/SQL procedure successfully completed.

小结：同样的数据经过重组数据进行压缩，可以比未压缩时节约了4倍空间，在一些特定场合使用压缩技术还是非常有效果的。

实验 5   给分区表进行压缩，每个分区都可以独立压缩

LS@LEO> create table leo_test_partition1 (object_id,object_name)             我们先创建一个分区表，共6个分区

partition by range (object_id)

(

partition leo_p1 values less than (1000),

partition leo_p2 values less than (2000),

partition leo_p3 values less than (3000),

partition leo_p4 values less than (4000),

partition leo_p5 values less than (5000),

partition leo_max values less than (maxvalue)

)

as select object_id,object_name from dba_objects;

Table created.

LS@LEO> insert into leo_test_partition1 select * from leo_test_partition1;    多次加载数据

LS@LEO> select count(*) from leo_test_partition1;                             33万行

COUNT(*)

----------

332576

LS@LEO> select count(*) from leo_test_partition1 partition (leo_p2);          leo_p2分区有32000行

COUNT(*)

----------

32000

LS@LEO> commit;                                                               提交

LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_TEST_PARTITION1';   6个分区都是未压缩状态

PARTITION_NAME                 COMPRESS

------------------------------ -------- --------

LEO_MAX                       DISABLED

LEO_P1                         DISABLED

LEO_P2                         DISABLED

LEO_P3                         DISABLED

LEO_P4                         DISABLED

LEO_P5                         DISABLED

LS@LEO> execute show_space('LEO_TEST_PARTITION1',USER,'TABLE PARTITION','LEO_P2');        leo_p2分区未压缩时空间情况

Unformatted Blocks .....................               0

FS1 Blocks (0-25)  .....................                 0

FS2 Blocks (25-50) .....................                0

FS3 Blocks (50-75) .....................                0

FS4 Blocks (75-100).....................               5

Full Blocks        .....................                      105                                  其中用了105个装满数据

Total Blocks............................                    120                                  占用了120个数据块

Total Bytes.............................                    983,040                                  0.9375M

Total MBytes............................                   0

Unused Blocks...........................                 0

Unused Bytes............................                 0

Last Used Ext FileId....................                 7

Last Used Ext BlockId...................              10,601

Last Used Block.........................                 8

PL/SQL procedure successfully completed.

LS@LEO> alter table leo_test_partition1 move partition leo_p2 compress; 单独对leo_p2分区进行压缩，结尾必须添加compress关键字否则不做压缩

Table altered.

LS@LEO> select partition_name,compression from user_tab_partitions where table_name='LEO_TEST_PARTITION1';  leo_p2分区已经压缩

PARTITION_NAME                 COMPRESS

------------------------------ --------

LEO_MAX                       DISABLED

LEO_P1                         DISABLED

LEO_P2                         ENABLED

LEO_P3                         DISABLED

LEO_P4                         DISABLED

LEO_P5                         DISABLED

LS@LEO> execute show_space('LEO_TEST_PARTITION1',USER,'TABLE PARTITION','LEO_P2');         leo_p2分区是压缩时空间情况

Unformatted Blocks .....................               0

FS1 Blocks (0-25)  .....................                 0

FS2 Blocks (25-50) .....................                0

FS3 Blocks (50-75) .....................                0

FS4 Blocks (75-100).....................               0

Full Blocks        .....................                      104                                   其中用了104个装满数据，只比原来少了1个块

Total Blocks............................                    120                                   占用了120个数据块

Total Bytes.............................                    983,040

Total MBytes............................                  0

Unused Blocks...........................                 6

Unused Bytes............................                49,152

Last Used Ext FileId....................               7

Last Used Ext BlockId...................             11,297

Last Used Block.........................                 2

PL/SQL procedure successfully completed.

小结：我们看到leo_p2分区压缩时比未压缩时只在使用的数据块上少了一个，感觉基本上没怎么压，后来分析数据发现，记录重复率非常小，样本数据条数也不够大，导致压缩比也小，效果稍差。

原理出处：表压缩比：列中数据重复率越高，压缩比越大，列中数据重复率越低，压缩比越小

大家在做测试的时候可以和我的结果对比一下，看看是不是一样，如果分析的不足，请提出宝贵意见，共同进步：)

十、数据压缩应用场景

1.静态数据：这种数据处在不在改变的状态，只用于查询和分析，可以压缩

2.历史数据：这种数据当前已经不在使用，只用于以后归档查询，可以压缩

3.海量数据：提高数据处理效率，减少内存和磁盘I/O开销，可以压缩

4.备份数据：压缩备份可以节约大量空间给其他对象使用，可以压缩

Leonarding

2012.8.14

天津&summer

分享技术~收获快乐

Blog：http://space.itpub.net/26686207