ITPub博客

首页 > 数据库 > Oracle > 验证数据压缩对DML的影响

验证数据压缩对DML的影响

原创 Oracle 作者:buptdream 时间:2014-08-06 15:50:48 0 删除 编辑
实际上,只有以下几种情况会发生数据的压缩处理,其他的DML是不会对数据进行压缩的。
alter table move
create table as select
insert /*+append*/
sqlldr+direct path
正常的insert的数据是不会压缩的:

SQL> conn change/change
Connected.
SQL> create table change1 compress as select * from dba_objects;

Table created.

SQL> create table change2  compress  as select * from dba_objects where 1=2;

Table created.

SQL> insert into change2 select * from dba_objects;

72301 rows created.

SQL> commit;

Commit complete.

下面我们通过show_space()来查看change1,change2表占用的数据块的情况
SQL> set serveroutput on;
SQL> exec show_space('CHANGE1');
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        .....................             296   
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................              74
Unused Bytes............................         606,208
Last Used Ext FileId....................               5
Last Used Ext BlockId...................       1,455,872
Last Used Block.........................              54

PL/SQL procedure successfully completed.


SQL> exec show_space('CHANGE2');
Unformatted Blocks .....................              48
FS1 Blocks (0-25)  .....................               2
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              29
Full Blocks        .....................             921
Total Blocks............................           1,024
Total Bytes.............................       8,388,608
Total MBytes............................               8
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               5
Last Used Ext BlockId...................       1,456,896
Last Used Block.........................             128
从上面可以看出change2并没有进行压缩,也就说insert into进去的数据是不能进行压缩的。那我们来实验一下/*+append*/来看一下:


PL/SQL procedure successfully completed.
SQL> truncate table change2;

Table truncated.

SQL>  exec show_space('CHANGE2');
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        .....................               0
Total Blocks............................               8
Total Bytes.............................          65,536
Total MBytes............................               0
Unused Blocks...........................               5
Unused Bytes............................          40,960
Last Used Ext FileId....................               5
Last Used Ext BlockId...................       1,455,736
Last Used Block.........................               3

PL/SQL procedure successfully completed.

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

72301 rows created.

SQL> commit;

Commit complete.

SQL>  exec show_space('CHANGE2');
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        .....................             296
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................              74
Unused Bytes............................         606,208
Last Used Ext FileId....................               5
Last Used Ext BlockId...................       1,456,256
Last Used Block.........................              54

PL/SQL procedure successfully completed.
送上面可以看出append进去的数据是经过压缩了。和前面的change1所占用的块数是一样的。
对于update操作来说,如果数据本身已经被压缩了,如果进行update修改的数据将不再被压缩,也就是说,update操作会对所操作的数据进行解压缩。
下面看一下CHANGE1为一个压缩表:
SQL> exec show_space('CHANGE1');
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        .....................             296
Total Blocks............................             384
Total Bytes.............................       3,145,728
Total MBytes............................               3
Unused Blocks...........................              74
Unused Bytes............................         606,208
Last Used Ext FileId....................               5
Last Used Ext BlockId...................       1,455,872
Last Used Block.........................              54

PL/SQL procedure successfully completed.

 下面我们对object_name进行update操作。
SQL> update change1 set object_name=object_name||'decompress';

72300 rows updated.

SQL> commit;

Commit complete.
下面是update后的数据块占用情况。可以见到存储空间变大了。

SQL>  exec show_space('CHANGE1');
Unformatted Blocks .....................              62
FS1 Blocks (0-25)  .....................               1
FS2 Blocks (25-50) .....................               0
FS3 Blocks (50-75) .....................               0
FS4 Blocks (75-100).....................              49
Full Blocks        .....................           1,014
Total Blocks............................           1,152
Total Bytes.............................       9,437,184
Total MBytes............................               9
Unused Blocks...........................               0
Unused Bytes............................               0
Last Used Ext FileId....................               5
Last Used Ext BlockId...................       1,457,024
Last Used Block.........................             128

PL/SQL procedure successfully completed.

下面我们通过alter table move compress方式来进行压缩。
SQL> alter table change1 move compress;

Table altered.

SQL> exec show_space('CHANGE1');
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        .....................             400
Total Blocks............................             512
Total Bytes.............................       4,194,304
Total MBytes............................               4
Unused Blocks...........................              96
Unused Bytes............................         786,432
Last Used Ext FileId....................               5
Last Used Ext BlockId...................       1,457,536
Last Used Block.........................              32

PL/SQL procedure successfully completed.
我们可以看到,表压缩后空间占用情况明显减少,但是比update之前的空间占用要多一些,原因就是update时将记录的长度变大了,这样占用的空间就会相应的增加,由于
update会对压缩的数据进行解压缩处理,所以对于update操作的数据,使用数据压缩技术是不合适的,因为这种解压缩会额外的消耗更多的cpu,会直接影响到update操作的效率。
而inert 操作不会受到compresss属性影响,插入的数据不会被压缩。

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

请登录后发表评论 登录
全部评论

注册时间:2008-12-04

  • 博文量
    25
  • 访问量
    335699