Archive for March 13th, 2008
10G的compress表测试
Thursday, March 13th, 2008
做了个10G中的压缩表测试，发现压缩比例还是比较高的，关键看数据是否按照重复性高的列在排序
也对压缩表和非压缩表查询进行了对比，测试结果表明解压还是存在不少的CPU消耗，时间居然比普通表要久，
但数据很多是经过cache的,相信在IO比较多的查询上，IO量的节省带来的效益应该可以盖过CPU计算产生的消耗
cnckweblog@DWALI> create table nocompress_10G as select * from all_objects;
Table created.
cnckweblog@DWALI>create table compress_10G nologging compress as select * from nocompress_10G;
Table created.
cnckweblog@DWALI> select SEGMENT_NAME,sum(BYTES) from user_extents where segment_name in (’NOCOMPRESS_10G’,’COMPRESS_10G’) group by SEGMENT_NAME;
SEGMENT_NAME SUM(BYTES)
—————————— ———-
NOCOMPRESS_10G 2134900736
COMPRESS_10G 639631360
全表扫描，压缩表比较有优势
cnckweblog@DWALI>select count(*) from nocompress_10G;
17995776
1 row selected.
Elapsed: 00:00:05.69
cnckweblog@DWALI>select count(*) from compress_10G;
17995776
1 row selected.
Elapsed: 00:00:01.50
owner列应该是高度压缩的，选择他会引起CPU的消耗，结果压缩表比较慢
cnckweblog@DWALI>select count(*) from nocompress_10G where owner = [...]
