九、数据压缩和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