Exadata 之混合列式压缩（摘录加总结）

Exadata 混合列式压缩

HCC works by storing data in a nontraditional format—nontraditional for Oracle, anyway. Data stored using HCC still resides in Oracle blocks, and each block still has a block header. But the data storage has been reorganized. In the first place, the blocks are combined into logical structures called compression

units, or CUs. A CU consists of multiple Oracle blocks (usually adding up to 32K or 64K).

 

• 压缩单元是跨多个数据库块的逻辑结构。

• 压缩单元内的每一行都是独立的。

• 在加载数据时按列组织数据。

• 每一列都分别进行压缩。

• 支持智能扫描。

• 无论要读取哪一行完整的数据，都可以在一个压缩单元中完成。

 

CREATE TABLE ... COMPRESS FOR QUERY LOW;

CREATE TABLE ... COMPRESS FOR QUERY HIGH;

CREATE TABLE ... COMPRESS FOR ARCHIVE LOW;

CREATE TABLE ... COMPRESS FOR ARCHIVE HIGH;

 

HCC性能

A 加载性能

         压缩的时间会随着压缩的比例的增大而增加

B 查询性能

         涉及到压缩的查询性能是个很复杂的问题，压缩可能使查询变快也可能变慢。虽然解压缩一定会在cpu利用率方面增加额外的开销，但是对于磁盘访问时瓶颈的查询来说，往往会被减少读取的块数量所抵消，并且在很多情况下，获得的收益大于付出的开销。

C  DML性能

     Generally speaking, records that will be updated should not be compressed. When you update a record

in an HCC table, the record will be migrated to a new a block that is flagged as an OLTP compressed

block. Of course, a pointer will be left behind so that you can still get to the record via its old rowid, but

the record will be assigned a new rowid as well. Since updated records are downgraded to OLTP

compression you need to understand how that compression mechanism works on updates. Figure 3-6

demonstrates how non-direct path loads into an OLTP block are processed.

 

 

Summary

Introduced in Oracle 11g R2, Hybrid Columnar Compression provides exceptional compression

capabilities that are far beyond anything available in prior releases. This is thanks in large part to the

adoption of industry-standard compression algorithms and an increase in the size of the compression

unit from a single database block (typically 8K) to a larger unit of 32K or 64K. The feature is only

appropriate for data that is no longer being modified, though, because of locking issues and the fact that

updated rows are moved into a much less compressed format (OLTP compression format). For this

reason, HCC should only be used with data that is no longer being modified (or only occasionally

modified). Since compression can be defined at the partition level, it is common to see tables that have a

mixture of compressed and uncompressed partitions. This technique can in many cases replace ILM

approaches that require moving data to alternate storage media and then purging it from the database.

 

1.JPG

2.JPG

3.JPG

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

转载于:http://blog.itpub.net/758322/viewspace-741978/