oracle 表压缩技术，oracle压缩技术

oracle 表压缩技术，oracle压缩技术

   压缩表是我们维护管理中经常会用到的，下面我们看都oracle给我们提供了哪些压缩方式。文章摘自“Oracle® Database Administrator's Guide11g Release 2 (11.2)”。因为Hybrid Columnar Compression压缩只有在Exadata上才支持，目前用的比较少，我们会重点说一下basic和oltp两种压缩方式。

 

Consider Using Table Compression

As your database grows in size, consider using table compression. Compression saves disk space, reduces memory use in the database buffer cache, and can significantly speed query execution during reads. Compression has a cost in CPU overhead for data loading and DML. However, this cost might be offset by reduced I/O requirements.

##随着数据的增加，可以考虑使用表压缩技术。压缩能够节省磁盘空间，减少SGA中buffer cache的使用，显著提高查询时读数据的效率。对压缩过后的表进行数据导入和DML需要消耗更多的cpu资源，但是压缩的使用减少了I/O的开销，可以抵消掉额外的cpu开销成本(根据系统的情况，如果你的库本来cpu资源已经不足，那么这样做就不合适了)

Table compression is completely transparent to applications. It is useful in decision support systems (DSS), online transaction processing (OLTP) systems, and archival systems.

##压缩技术对应用来说完全是透明的。它对dss，oltp，archival system等系统等很有用

You can specify compression for a tablespace, a table, or a partition. If specified at the tablespace level, then all tables created in that tablespace are compressed by default.

##你可以为表空间，表或者分区指定压缩属性。如果你为表空间指定了压缩属性，那么在该表空间下建的表会默认的继承压缩属性。

Compression can occur while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:

##压缩在insert，update和批量加载时都可能发生(具体要看你使用的是哪种压缩方式)

• Single-row or array inserts and updates

• The following direct-path INSERT methods:

  • Direct path SQL*Loader

  • CREATE TABLE AS SELECT statements

  • Parallel INSERT statements

  • INSERT statements with an APPEND or APPEND_VALUES hint

Oracle Database supports several methods of table compression. They are summarized in Table 20-1.

##oracle数据库支持如下几种压缩方式(注意，Hybrid Columnar Compression模式的压缩只有在Exadata上才支持)

Table 20-1 Table Compression Methods

Table Compression Method	Compression Level	CPU Overhead	Applications	Notes
Basic compression	High	Minimal	DSS	None.
OLTP compression	High	Minimal	OLTP, DSS	None.
Warehouse compression (Hybrid Columnar Compression)	Higher	Higher	DSS	The compression level and CPU overhead depend on compression level specified (LOW or HIGH).
Archive compression (Hybrid Columnar Compression)	Highest	Highest	Archiving	The compression level and CPU overhead depend on compression level specified (LOW or HIGH).

When you use basic compression, warehouse compression, or archive compression, compression only occurs when data is bulk loaded into a table.

##当你使用的是basic，warehouse或者archive压缩时，只有批量载入的数据才会被压缩(此处我觉得有点问题，应该是只有直接路径加载的数据才会被压缩。不知道官方文档这里说的批量加载是否指的就是直接路径加载)

When you use OLTP compression, compression occurs while data is being inserted, updated, or bulk loaded into a table. Operations that permit compression include:

##当你使用的是oltp压缩时，传统路径插入和直接路径插入的数据都会被压缩

• Single-row or array inserts and updates##这里就是指的传统路径插入

• The following direct-path INSERT methods:##这里指的是直接路径插入

  • Direct path SQL*Loader

  • CREATE TABLE AS SELECT statements

  • Parallel INSERT statements

  • INSERT statements with an APPEND or APPEND_VALUES hint

Basic compression compresses data inserted by direct path load only and supports limited data types and SQL operations. OLTP compression is intended for OLTP applications and compresses data manipulated by any SQL operation.

##Basic压缩方式仅支持有限的数据类型和sql操作，当数据使用直接路径插入时，这些数据会被压缩。OLTP压缩一般用在oltp系统上，能够压缩各种sql操作产生的数据

Warehouse compression and archive compression achieve the highest compression levels because they use Hybrid Columnar Compression technology. Hybrid Columnar Compression technology uses a modified form of columnar storage instead of row-major storage. This enables the database to store similar data together, which improves the effectiveness of compression algorithms. For data that is updated, Hybrid Columnar Compression uses more CPU and moves the updated rows to row format so that future updates are faster. Because of this optimization, you should use it only for data that is updated infrequently.

##因为使用了混合列模式压缩技术，Warehouse 和 archive能够提供最高的压缩等级。混合列模式压缩技术使用列模式存储代替了之前的行模式存储。列模式存储使用数据库能够把相似的数据存储在一起从而提高压缩的效率。如果混合列模式压缩的数据被更新，那么会消耗额外的cpu把被更新的行转变成行模式(行的rowid会发生变化)，速度也是比较快的(不知道此处的快是跟行模式压缩相比，还是同非压缩表的update对比？？？)。基于混合列模式压缩的优化原理，我们应该在那些很少被update的表上使用这种压缩方式。

The higher compression levels of Hybrid Columnar Compression are achieved only with data that is direct-path inserted. Conventional inserts and updates are supported, but cause rows to be moved from columnar to row format, and reduce the compression level.

Table 20-2 lists characteristics of each table compression method.

Table 20-2 Table Compression Characteristics

Table Compression Method	CREATE/ALTER TABLE Syntax	Direct-Path INSERT	Notes
Basic compression	COMPRESS [BASIC]	Rows are compressed with basic compression.	COMPRESS and COMPRESS BASIC are equivalent. Rows inserted without using direct-path insert and updated rows are uncompressed.
OLTP compression	COMPRESS FOR OLTP	Rows are compressed with OLTP compression.	Rows inserted without using direct-path insert and updated rows are compressed using OLTP compression.
Warehouse compression (Hybrid Columnar Compression)	COMPRESS FOR QUERY [LOW|HIGH]	Rows are compressed with warehouse compression.	This compression method can result in high CPU overhead. Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level.
Archive compression (Hybrid Columnar Compression)	COMPRESS FOR ARCHIVE [LOW|HIGH]	Rows are compressed with archive compression.	This compression method can result in high CPU overhead. Updated rows and rows inserted without using direct-path insert are stored in row format instead of column format, and thus have a lower compression level.

You specify table compression with the COMPRESS clause of the CREATE TABLE statement. You can enable compression for an existing table by using these clauses in an ALTER TABLE statement. In this case, only data that is inserted or updated after compression is enabled is compressed. Similarly, you can disable table compression for an existing compressed table with the ALTER TABLE...NOCOMPRESS statement. In this case, all data that was already compressed remains compressed, and new data is inserted uncompressed.

##你可以在建表的时候指定compress字句，也可以在表创建后使用alter table语句改变表的压缩属性。如果是后者，那么表中已存在的数据不会被压缩，只有新插入或者update的数据有可能被压缩。同样的如果你改变一个压缩表的属性为非压缩表，表中已经存在的被压缩的数据还是保持压缩的状态，不会被解压，但新插入的数据将不会再被压缩。

The COMPRESS FOR QUERY HIGH option is the default data warehouse compression mode. It provides good compression and performance when using Hybrid Columnar Compression on Exadata storage. The COMPRESS FOR QUERY LOW option should be used in environments where load performance is critical. It loads faster than data compressed with the COMPRESS FOR QUERY HIGH option.

 

The COMPRESS FOR ARCHIVE LOW option is the default archive compression mode. It provides a high compression level and is ideal for infrequently-accessed data. The COMPRESS FOR ARCHIVE HIGH option should be used for data that is rarely accessed.

A compression advisor, provided by the DBMS_COMPRESSION package, helps you determine the expected compression level for a particular table with a particular compression method.

Note:

Hybrid Columnar Compression is dependent on the underlying storage system. See Oracle Database Licensing Information for more information.

See Also:

• Oracle Database Concepts for an overview of table compression

• "Compressed Tablespaces"

•  

Examples Related to Table Compression

The following examples are related to table compression:

• Example 20-1, "Creating a Table with OLTP Table Compression"

• Example 20-2, "Creating a Table with Basic Table Compression"

• Example 20-3, "Using Direct-Path Insert to Insert Rows Into a Table"

• Example 20-4, "Creating a Table with Warehouse Compression"

• Example 20-5, "Creating a Table with Archive Compression"

Example 20-1 Creating a Table with OLTP Table Compression

##新建一个oltp类型的压缩表

The following example enables OLTP table compression on the table orders:

CREATE TABLE orders  ...  COMPRESS FOR OLTP;

Data for the orders table is compressed during both direct-path INSERT and conventional DML.

##此种压缩方式下，直接路径插入和传统路径插入的数据都会被压缩。

 

Example 20-2 Creating a Table with Basic Table Compression

##新建一个basic类型的压缩表

The following statements, which are equivalent, enable basic table compression on the sales_history table, which is a fact table in a data warehouse:

CREATE TABLE sales_history  ...  COMPRESS BASIC;

CREATE TABLE sales_history  ...  COMPRESS;

Frequent queries are run against this table, but no DML is expected.

##这种压缩方式一般被用在频繁查询，但不被dml的表上

 

Example 20-3 Using Direct-Path Insert to Insert Rows Into a Table

This example demonstrates using the APPEND hint to insert rows into the sales_history table using direct-path INSERT.

##使用append提示符激活直接路径插入

INSERT /*+ APPEND */ INTO sales_history SELECT * FROM sales WHERE cust_id=8890;
COMMIT;

Example 20-4 Creating a Table with Warehouse Compression

This example enables Hybrid Columnar Compression on the table sales_history:

CREATE TABLE sales_history  ...  COMPRESS FOR QUERY;

The table is created with the default COMPRESS FOR QUERY HIGH option. This option provides a higher level of compression than basic or OLTP compression. It works well when load performance is critical, frequent queries are run against this table, and no DML is expected.

Example 20-5 Creating a Table with Archive Compression

The following example enables Hybrid Columnar Compression on the table sales_history:

CREATE TABLE sales_history  ...  COMPRESS FOR ARCHIVE;

The table is created with the default COMPRESS FOR ARCHIVE LOW option. This option provides the highest level of compression and works well for infrequently-accessed data.

 

Compression and Partitioned Tables

A table can have both compressed and uncompressed partitions, and different partitions can use different compression methods. If the compression settings for a table and one of its partitions do not match, then the partition setting has precedence for the partition.

##一个分区表可以同时包含压缩的和非压缩的分区，并且可以给压缩分区指定不同的压缩方式。我们可以给分区表以及分区指定不同的分区方式，并且分区上的指定具有较高的优先级。

To change the compression method for a partition, do one of the following:

##改变分区压缩方式的方法如下：

• To change the compression method for new data only, use ALTER TABLE ... MODIFY PARTITION ... COMPRESS ...##仅对新数据有效

• To change the compression method for both new and existing data, use either ALTER TABLE ... MOVE PARTITION ... COMPRESS ... or online table redefinition.##使用alter table...move partition...compress或者表的在线重定义方式改变表的压缩方式，对新的数据和旧的数据都是有效的。

•  

Determining If a Table Is Compressed

In the *_TABLES data dictionary views, compressed tables have ENABLED in the COMPRESSION column. For partitioned tables, this column is null, and the COMPRESSION column of the *_TAB_PARTITIONS views indicates the partitions that are compressed. In addition, the COMPRESS_FOR column indicates the compression method in use for the table or partition.

##在*_TABLES视图中压缩表的compress列被标记为"ENABLED"，当然如果是分区表的话，那么这一列被标记为空。分区表及组合分区表的压缩情况我们相应的应该查看*_TAB_PARTITIONS及*_TAB_SUBPARTITIONS视图

SQL> SELECT table_name, compression, compress_for FROM user_tables;
 
TABLE_NAME       COMPRESSION   COMPRESS_FOR
---------------- ------------  -----------------
T1               DISABLED
T2               ENABLED       BASIC
T3               ENABLED       OLTP
T4               ENABLED       QUERY HIGH
T5               ENABLED       ARCHIVE LOW

SQL> SELECT table_name, partition_name, compression, compress_for
  FROM user_tab_partitions;

TABLE_NAME  PARTITION_NAME   COMPRESSION   COMPRESS_FOR
----------- ---------------- -----------   ------------------------------
SALES       Q4_2004          ENABLED       ARCHIVE HIGH
  ...
SALES       Q3_2008          ENABLED       QUERY HIGH
SALES       Q4_2008          ENABLED       QUERY HIGH
SALES       Q1_2009          ENABLED       OLTP
SALES       Q2_2009          ENABLED       OLTP

 

Determining Which Rows Are Compressed

When Hybrid Columnar Compression tables are updated, the rows change to a lower level of compression, such as from warehouse compression (QUERY HIGH) to OLTP compression or no compression. To determine the compression level of a row, use the GET_COMPRESSION_TYPE function in the DBMS_COMPRESSION package.

##当混合列模式压缩的表被更新，被更新的行的压缩等级会降低，如从warehouse compression (QUERY HIGH)降至oltp或者非压缩。我们可以使用DBMS_COMPRESSION宝中的GET_COMPRESSION_TYPE function去查询某一列的压缩等级。

For example, the following query returns the compression type for a row in the hr.employees table:

SELECT DECODE(DBMS_COMPRESSION.GET_COMPRESSION_TYPE(
                 ownname => 'HR', 
                 tabname => 'EMPLOYEES', 
                 row_id  => 'AAAVEIAAGAAAABTAAD'), 
   1,  'No Compression',
   2,  'Basic or OLTP Compression', 
   4,  'Hybrid Columnar Compression for Query High',
   8,  'Hybrid Columnar Compression for Query Low',
   16, 'Hybrid Columnar Compression for Archive High',
   32, 'Hybrid Columnar Compression for Archive Low',
   'Unknown Compression Type') compression_type
FROM DUAL;

By sampling the table rows, you can determine the percentage of rows that are no longer at the higher compression level. You can use ALTER TABLE or MOVE PARTITION to specify a higher compression level. For example, if 10 percent of the rows are no longer at the highest compression level, then you might alter the table or move the partition to specify a higher compression level.

See Also:

Oracle Database PL/SQL Packages and Types Reference for additional information about GET_COMPRESSION_TYPE  

Changing the Compression Level

You can change the compression level for a partition, table, or tablespace. For example, suppose a company uses warehouse compression for its sales data, but sales data older than six months is rarely accessed. If the sales data is stored in a table that is partitioned based on the age of the data, then the compression level for the older data can be changed to archive compression to free disk space.

If a table is partitioned, then the DBMS_REDEFINITION package can change the compression level of the table. This package performs online redefinition of a table by creating a temporary copy of the table that holds the table data while it is being redefined. The table being redefined remains available for queries and DML statements during the redefinition. The amount of free space for online table redefinition depends on the relative compression level of the existing table and the new table. Ensure you have enough hard disk space on your system before using the DBMS_REDEFINITION package.

If a table is not partitioned, then you can use the ALTER TABLE...MOVE...COMPRESS FOR... statement to change the compression level. The ALTER TABLE...MOVE statement does not permit DML statements against the table while the command is running.

To change the compression level for a partition, use the ALTER TABLE...MODIFY PARTITION statement. To change the compression level for a tablespace, use the ALTER TABLESPACE statement.

##对于非分区表我们只能通过ALTER TABLE...MOVE...COMPRESS FOR命令来改变表的压缩等级，在变更的过程中不允许对表进行DML操作(如果表很大的话，就会长时间的影响应用)。如果是分区表我们可以通过上面的方式来做，也可以通过使用DBMS_REDEFINITION包来改变表的压缩等级，使用在线重定义的时候会为目标表建一个临时备份来保存表中的数据，在线重定义过程中目标是一直可以访问和dml的。

See Also:

• "Moving a Table to a New Segment or Tablespace" for additional information about the ALTER TABLE command

• Oracle Database PL/SQL Packages and Types Reference for additional information about the DBMS_REDEFINITION package

•  

Adding and Dropping Columns in Compressed Tables

The following restrictions apply when adding columns to compressed tables:

##向压缩表中添加列的时候有如下限制：

• Basic compression—You cannot specify a default value for an added column.##对于basic压缩方式你不能为添加的列指定默认值

• OLTP compression—If a default value is specified for an added column, then the column must be NOT NULL. Added nullable columns with default values are not supported.##

The following restrictions apply when dropping columns in compressed tables:

##从压缩表中删除列时有如下限制：

• Basic compression—Dropping a column is not supported.##对于basic压缩来说不支持列删除

• OLTP compression—DROP COLUMN is supported, but internally the database sets the column UNUSED to avoid long-running decompression and recompression operations.##对于oltp压缩也是不支持列删除，但是我们可以把列设置为UNUSED的，避免解压缩和重新压缩操作队它的影响。

•  

Exporting and Importing Hybrid Columnar Compression Tables

Hybrid Columnar Compression tables can be imported using the impdp command of the Data Pump Import utility. By default, the impdp command preserves the table properties, and the imported table is a Hybrid Columnar Compression table. On tablespaces not supporting Hybrid Columnar Compression, the impdp command fails with an error. The tables can also be exported using the expdp command.

You can import the Hybrid Columnar Compression table as an uncompressed table using the TRANSFORM:SEGMENT_ATTRIBUTES=n option clause of the impdp command.

An uncompressed or OLTP-compressed table can be converted to Hybrid Columnar Compression format during import. To convert a non-Hybrid Columnar Compression table to a Hybrid Columnar Compression table, do the following:

See Also:

• Oracle Database Utilities for additional information about the Data Pump Import utility

• Oracle Database SQL Language Reference for additional information about the ALTER TABLESPACE command

•  

Restoring a Hybrid Columnar Compression Table

There may be times when a Hybrid Columnar Compression table must be restored from a backup. The table can be restored to a system that supports Hybrid Columnar Compression, or to a system that does not support Hybrid Columnar Compression. When restoring a table with Hybrid Columnar Compression to a system that supports Hybrid Columnar Compression, restore the file using Oracle Recovery Manager (RMAN) as usual.

When a Hybrid Columnar Compression table is restored to a system that does not support Hybrid Columnar Compression, you must convert the table from Hybrid Columnar Compression to OLTP compression or an uncompressed format. To restore the table, do the following:

See ALso:

• Oracle Database Backup and Recovery User's Guide for additional information about RMAN

• Oracle Database SQL Language Reference for additional information about the ALTER TABLE command

•  

Notes and Other Restrictions for Compressed Tables

The following are notes and restrictions related to compressed tables:

• Online segment shrink is not supported for compressed tables.##压缩表不支持在线segment shrink操作

• The table compression methods described in this section do not apply to SecureFiles large objects (LOBs). SecureFiles LOBs have their own compression methods. See Oracle Database SecureFiles and Large Objects Developer's Guide for more information.##本节所说的压缩方式不能再SecureFiles large objects上使用，SecureFiles LOBs有自己的压缩方式

• Compression technology uses CPU. Ensure that you have enough available CPU to handle the additional load.##压缩操作对cpu的消耗比较高，确保你的cpu资源够用

• Tables created with basic compression have the PCT_FREE parameter automatically set to 0 unless you specify otherwise.##建表时指定basic压缩方式PCT_FREE值为0，当然你也可以特别指定其他的你认为合适的值。(建表时指定oltp压缩方式，PCT_FREE值默认会被设置为10)

•  

Packing Compressed Tables

If you use conventional DML on a table compressed with basic compression or Hybrid Columnar Compression, then all inserted and updated rows are stored uncompressed or in a less-compressed format. To "pack" the compressed table so that these rows are compressed, use an ALTER TABLE MOVE statement. This operation takes an exclusive lock on the table, and therefore prevents any updates and loads until it completes. If this is not acceptable, then you can use online table redefinition.

See Also:

• Oracle Database SQL Language Reference for more details on the CREATE TABLE...COMPRESS, ALTER TABLE...COMPRESS, and ALTER TABLE...MOVE statements, including restrictions

• Oracle Database VLDB and Partitioning Guide for more information on table partitioning

• "Improving INSERT Performance with Direct-Path INSERT"

• "Redefining Tables Online"

给你copy一段吧

数据使用的现状随着企业业务的快速增长，企业数据库中存储的数据出现了大幅度的增加，并且随着支撑企业运行的商业智能平台的实施，数据中心的数据更是出现了爆炸式增长，虽然磁盘存储器的价格在不断的下降，但是海量的数据存储也是企业IT成本不可轻视的一个重要组成。更重要的是，企业的IT管理者需要确保在业务数据量不断增长的情况下性能的稳定和系统的可扩展性。

Oracle Database 11g引入的高级压缩特征可以帮助客户最大化资源使用率并减少成本。它是的IT管理者可以通过为所有类型的数据启用压缩来减少数据库的存储容量，虽然高级压缩的最明显的好处是减少存储成本，而且Oracle Database 11g的高级压缩技术被设计为减少IT架构所有组件的成本，包括内存和网络带宽。

Oracle压缩概述

Oracle的压缩经历了以下几个版本：

Oracle 8i简单的索引压缩；

Oracle9ir2表级别的压缩；

Oracle 10g LOB压缩；

Oracle11g行级别压缩；

相对于9iR2引入的表数据压缩技术只能用于通过批量加载数据，而传统的DML的数据不会压缩，11g的表数据压缩真正支持了各种DML语句从而也能够用于OLTP环境（insert，update等）。

而且Oracle11g的压缩使用了“基于数据块的批次压缩技术”，也就是说数据的压缩不是在执行DML语句的时候实时发生的，一开始当一个数据块空的时候，插入的数据并不被压缩，所以这个时候对于性能是没有影响的，数据块压缩的动作是在数据块即将满的时候才发生，这样就保证了大部分DML语句的效率，并且，因为在数据块将要满的时候才发生的压缩技术保证了最大的压缩率，因为实际上压缩是基于数据块里相同的数据的。压缩对比如下：

Oracle11g的压缩选项

OLTP压缩

允许在所有的DML操作包括INSERT，UPDATE，DELETE期间进行压缩。该特性使得可以为所有应用启用压缩，并使用了智能的算法确保写操作期间的负载最小化（随后我们将看到）。并且，压缩的数据使用更少的磁盘空间并且能够更有效的利用内存，并通过减少磁盘I/O来提高查询的性能。

非结构化文件副本清除

Oracle11g压缩技术采用了智能的技术来消除存储在Oracle Database 11g中的文件拷贝，它不仅减少了存储，并且在包含重复内容的写和拷贝期间能够极大地提高性能。

非结构化文件压缩

通过使用额外的CPU资源，我们可以在数据库内压缩无结构的数据和文件数据。

备份数据压缩

数据库备份的空间需求和备份性能直接受到数据库大小的影响，高级压缩使得在使用RMAN和Oracle Data Pump进行备份时直接包含压缩的备份数据来减少存储容量并提高性能。

网络传输压缩

高级压缩选项提供了功能用于压缩Oracle Data Guard重做数据，这可以提高网络带宽的利用率。

11g压缩特性的好处

Oracle11g的在线数据压缩工具提供了以下好处：

节省大量的磁盘：根据存储数据的不同，Oracle压缩将大量节省磁盘空间；

更快的全表扫描、区间扫描：因为数据存储在更少的数据块中，全表扫描和索引区间扫描能够使用更少的快提取行；

减少网络负载：因为数据仅在Oracle内压缩和解压缩，所以外部网络仅传输压缩的数据块。

随着数据的快速增长，企业必须快速的适应这些改变而不会降低性能以及提高成本。Oracle Database 11g高级压缩特性可以帮助客户减少管理大量数据的资源和成本，它提供了一个自......余下全文>>
 

没有实际使用过，

据说因为传递的数据量减小了，系统性能反而可能有一定提升。