eygle.com   eygle.com
eygle.com eygle

« 并行查询的 PX Deq: reap credit 等待 | Blog首页 | 11gR2新特性之二 - Flash Cache 的SSD支持 »

11gR2新特性之一-列式压缩(Columnar Compression)

在11gR2发布之前,已经从一些Beta测试中知道,新版本中包含了列式压缩(Columnar Compression)的新特性,这一特性是被期待已久的,Sybase IQ产品尤以列式存储而著称,现在Oracle也在列式压缩上引入了类似技术。


Columnar compression is a new feature being introduced in Oracle Database 11g
Release 2 (11.2). Using columnar compression, data can be compressed during bulk
load operations.


During the load process, data is transformed into a column-oriented
format and then compressed using a set of new compression algorithms. Different
levels of compression can be specified. Oracle Database selects the compression
algorithm according to the desired level of compression. Data transformation is
handled by Oracle Database internally and no application changes are required to use
columnar compression.


Columnar compression provides significantly higher compression ratios as compared
to previously available compression features of Oracle Database. It also has a higher
CPU cost both at load time and at access time. Due to higher compression ratios, data
consumes less space in the buffer cache, providing I/O benefits that offset the CPU


The overhead is relatively low for full table scans than for single row access.
All database operations (SQL queries, indexing, DMLs, DDLs, as well as features like
partitioning) work transparently against tables compressed using columnar
compression. This makes it possible to grow databases to large sizes and to store and
query that data at a fraction of cost.

During bulk-load operations, Oracle Database will compress the data being loaded when it is beneficial for performance. For small segments with very little data, no compression will occur even if you specify it. Oracle Database handles data transformation and compression internally and requires no application changes to use compression.

No special installation is required to configure this feature. However, in order to use this feature, the database compatibility parameter must be set to 11.2.0 or higher.


Additional compression technologies, including hybrid columnar compression, are available with Oracle Exadata Storage Server. See the Oracle Exadata documentation for more information.


在创建数据表时,可以通过增加新的COMPRESS FOR QUERY或者COMPRESS FOR ARCHIVE来启用混合列式压缩hybrid columnar compression)特性,文档说明如下:
When you specify COMPRESS FOR QUERY or COMPRESS FOR ARCHIVE, you enable hybrid columnar compression. With hybrid columnar compression, data can be compressed during bulk load operations. During the load process, data is transformed into a column-oriented format and then compressed. Oracle Database uses a compression algorithm appropriate for the level you specify. In general, the higher the level, the greater the compression ratio. Hybrid columnar compression can result in higher compression ratios, at a greater CPU cost. Therefore, this form of compression is recommended for data that is not frequently updated.

COMPRESS FOR QUERY is useful in data warehousing environments. Valid values are LOW and HIGH, with HIGH providing a higher compression ratio. The default is HIGH.

COMPRESS FOR ARCHIVE uses higher compression ratios than COMPRESS FOR QUERY, and is useful for compressing data that will be stored for long periods of time. Valid values are LOW and HIGH, with HIGH providing the highest possible compression ratio. The default is LOW.




ORA-64307: hybrid columnar compression is only supported in tablespaces residing on Exadata storage
Cause: An attempt was made to use hybrid columnar compression on unsupported storage.
Action: Create this table in a tablespace residing on Exadata storage or use a different compression type.

-The End-

    >> 2013-09-02文章:
    >> 2010-09-02文章:
    >> 2008-09-02文章:
    >> 2007-09-02文章:
    >> 2006-09-02文章:
    >> 2005-09-02文章:

By eygle on 2009-09-02 13:57 | Comments (6) | OraNews | Oracle12c/11g | 2383 |


这个特性离我们还有点距离。(大大的距离) :)
对于Exadata中有一个smart scan,上次记得来公司推荐.
这个smart scan对于大表取小部分数据时,会在存储上智能的判断是否符合条件,即不返回到实例的db_buffer中.
可是我在想如果smart scan要查询的大表中大量的数据如90%的数据,那么这个smart scan是不是反而影响了性能.

11.2里边有个新特性: Exadata Simulation
不知道能不能和Columnar Compression配合使用.


没有,不知道领导们谈成什么样,已经是long long ago 的事了.呵呵
现在我们这边的方案一般是来两个小型机 如sun的 m5000,每台虚拟出两个节点,两两做rac,然后再来个集中的存储,你要多少,他们划多少空间给你 :)
一切都往大,集中考虑,我觉得可能是为减小占用机房的可用空间 .

可是我在想如果smart scan要查询的大表中大量的数据如90%的数据,那么这个smart scan是不是反而影响了性能.


CopyRight © 2004~2020 云和恩墨,成就未来!, All rights reserved.
数据恢复·紧急救援·性能优化 云和恩墨 24x7 热线电话:400-600-8755 业务咨询:010-59007017-7040 or 7037 业务合作: marketing@enmotech.com