ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Compression One

Compression One

原创 Linux操作系统 作者:caisanpx 时间:2013-11-20 00:13:00 0 删除 编辑
Table Compression
The database can use table compression to reduce the amount of storage required for the table. Compression saves disk space, reduces memory use in the database buffer cache, and in some cases speeds query execution. Table compression is transparent to database applications.
数据库使用表压缩来减少表的存储数量。压缩能节约磁盘空间,减少在数据库缓冲区缓存中的内存消耗,某些情况下能加快查询执行。

1.Basic and OLTP Table Compression
Dictionary-based table compression provides good compression ratios for heap-organized tables. Oracle Database supports the following types of dictionary-based table compression:

1.1Basic table compression

    This type of compression is intended for bulk load operations. The database does not compress data modified using conventional DML. You must use direct path loads, ALTER TABLE . . . MOVE operations, or online table redefinition to achieve basic compression.
basic table compression旨在为大量的加载操作。数据库不能使用传统的DML来压缩被修改的数据。必须通过使用direct path load,alter table ..move或archive basic compression来压缩.

1.2OLTP table compression
    This type of compression is intended for OLTP applications and compresses data manipulated by any SQL operation.
OLTP table compression旨在为OLTP应用,可以通过SQL来进行操作。
For basic and OLTP table compression, the database stores compressed rows in row-major format. All columns of one row are stored together, followed by all columns of the next row. Duplicate values are replaced with a short reference to a symbol table stored at the beginning of the block. Thus, information needed to re-create the uncompressed data is stored in the data block itself.
对于basic,OLTP table compression来说,,数据库以row-major形式来存储压缩行。行与行之间存储在一起,一行接一行。重复的值存储在块开始处的象征表中。

Compressed data blocks look much like normal data blocks. Most database features and functions that work on regular data blocks also work on compressed blocks.

You can declare compression at the tablespace, table, partition, or subpartition level. If specified at the tablespace level, then all tables created in the tablespace are compressed by default.
你可以在tablespace,table,partition,subpartition 声明压缩等级,如果指定在tablespace级别,则表空间中所有表默认下都会被压缩。


The following example of a partial CREATE TABLE statement specifies OLTP compression for one partition and basic compression for the other partition:

CREATE TABLE sales (
    prod_id     NUMBER     NOT NULL,
    cust_id     NUMBER     NOT NULL, ... )
 PCTFREE 5 NOLOGGING NOCOMPRESS
 PARTITION BY RANGE (time_id)
 ( partition sales_2008 VALUES LESS THAN(TO_DATE(...)) COMPRESS BASIC,
   partition sales_2009 VALUES LESS THAN (MAXVALUE) COMPRESS FOR OLTP );


2.Hybrid Columnar Compression

With Hybrid Columnar Compression, the database stores the same column for a group of rows together. The data block does not store data in row-major format, but uses a combination of both row and columnar methods.
对于混合码压缩,数据库将相同column一起存储在一组行中,数据块不是以row-major形式存储,而是使用行与列组合的方法。

Storing column data together, with the same data type and similar characteristics, dramatically increases the storage savings achieved from compression. The database compresses data manipulated by any SQL operation, although compression levels are higher for direct path loads. Database operations work transparently against compressed objects, so no application changes are required.
数据库操作所透明对压缩对象,因此无需更改应用程序。



3.Types of Hybrid Columnar Compression

If your underlying storage supports Hybrid Columnar Compression, then you can specify the following compression types, depending on your requirements:

    Warehouse compression

    This type of compression is optimized to save storage space, and is intended for data warehouse applications.
    warehouse compression 优点在于节省存储空间。
    
    Online archival compression
    This type of compression is optimized for maximum compression levels, and is intended for historical data and data that does not change.
    online archivel compression 是最高的压缩等级,目的是为那些长时间的数据和不修改的数据。

To achieve warehouse or online archival compression, you must use direct path loads, ALTER TABLE . . . MOVE operations, or online table redefinition.
对于achive warehouse和online archivel compression你只能使用direct path加载,alter table ..move或online table redefinition.

Hybrid Columnar Compression is optimized for Data Warehousing and decision support applications on Exadata storage. Exadata maximizes the performance of queries on tables that are compressed using Hybrid Columnar Compression, taking advantage of the processing power, memory, and Infiniband network bandwidth that are integral to the Exadata storage server.
云服务器通过使用hybrid columnar compression来最大化查询性能,利用其的处理能力,内存和的Infiniband网络带宽。
Other Oracle storage systems support Hybrid Columnar Compression, and deliver the same space savings as on Exadata storage, but do not deliver the same level of query performance. For these storage systems, Hybrid Columnar Compression is ideal for in-database archiving of older data that is infrequently accessed.

4.Compression Units

Hybrid Columnar Compression uses a logical construct called a compression unit to store a set of rows. When you load data into a table, the database stores groups of rows in columnar format, with the values for each column stored and compressed together. After the database has compressed the column data for a set of rows, the database fits the data into the compression unit.
hybrid columnar compression用一个逻辑结构——compression unit来存储压缩的列。

For example, you apply Hybrid Columnar Compression to a daily_sales table. At the end of every day, you populate the table with items and the number sold, with the item ID and date forming a composite primary key. Table 2-1 shows a subset of the rows in daily_sales.



Assume that the rows in Table 2-1 are stored in one compression unit. Hybrid Columnar Compression stores the values for each column together, and then uses multiple algorithms to compress each column. The database chooses the algorithms based on a variety of factors, including the data type of the column, the cardinality of the actual values in the column, and the compression level chosen by the user.
假设该表中的行存储在一个压缩单元中,hybrid columnar compression将列存储在一起,然后使用多种算法去压缩列。

详细请参考
http://docs.oracle.com/cd/E11882_01/server.112/e40540/tablecls.htm#CNCPT608

关于COMPRESSION更详细的介绍,可参考COMPRESSION TWO
http://space.itpub.net/26844646/viewspace-777025/



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

上一篇: Compression Two
下一篇: Advanced Compression
请登录后发表评论 登录
全部评论

注册时间:2012-04-12

  • 博文量
    165
  • 访问量
    442643