eygle.com   eygle.com
eygle.com eygle
eygle.com  
 

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

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

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

显然对于具有大量重复值的Column,按照列来压缩可以极大缩减存储空间的耗用,这是Oracle在压缩技术上的又一增强,也是为了在细粒度解决用户问题的一个改进。

我们先看看此前Beta文档(文档号E10881-01)中的描述:
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.

这一改变对应用透明,Oracle可以根据用户的定义来决定压缩级别和算法,选择列式压缩的数据将以基于列的格式存储。

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
costs.

列式压缩的压缩率可能相当高,这也是以CPU换IO的做法之一,在节省了IO的同时会消耗更多的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.

Note:

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

在这里,Oracle声明,列式压缩的特性,仅在Exadata存储上可用,这一特性显然对数据仓库大有益处

在创建数据表时,可以通过增加新的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.

新的语法结构是这样的:

{ COMPRESS [ BASIC 
| FOR { OLTP
| { QUERY | ARCHIVE } [ LOW | HIGH ]
}
]
| NOCOMPRESS


}

同样这个错误提示让很多人为之一震,Exadata据说目前国内尚未有用户引入:
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 |

6 Comments

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

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

晕!还限定只能使用Exadata存储啦,看来oracle打算抢EMC的饭碗呵!

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

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

……
这位大哥,要是能查90%的数据,那还叫大表吗?所谓大表怎么也得几百万几千万行数据


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