实际上，只有以下几种情况会发生数据的压缩处理，其他的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/，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
