本帖最后由 kelsoncong 于 2012-3-21 10:53 编辑
刚刚在看Oracle Database 11g: BI and Data Warehousing的文档,对其中table compression的表述非常感兴趣,按照Oracle的说法,压缩表技术可以极大的减少存储空间,与此同时,不降低INSERT/UPDATE的性能,查询性能反而可以得到提高。之所以可以这样,是因为Oracle可以直接操作压缩数据。下面是Oracle的原文的描述:It is important to mention that table compression enhancements introduced in Oracle Database 11g are not just incremental changes. An enormous amount of work has gone into making sure that the new compression technology has negligible impact on updates because any noticeable write-time penalty due to compression will not be acceptable in an OLTP environment. As a result, compression technology in Oracle Database 11g is very efficient and could reduce the space consumption by 50–75%. And while you do that, not only your write performance does not degrade, but also your read performance or queries improve. This is because unlike desktop-based compression techniques where you have to wait for data to be uncompressed, Oracle technology reads the compressed data (less fetches needed) directly and does not require any uncompress operation.
这个是原理图:
Query PerformanceIn the previous section we see that compressed table (or partition, subpartition,index) segmentsrequire less space on disk. One may infer that this will improve query performance. It’s true. Let’s see how
hsensoy@dds> select count(*) from big_table_nocomp;
Elapsed: 00:00:28.99
hsensoy@dds> select count(*) from big_table_comp;
Elapsed: 00:00:11.75
hsensoy@dds> select count(*) from big_table_careful_comp;
Elapsed: 00:00:06.18