当前位置:首页 > 开发 > 数据库 > 正文

Oracle 11g Compress Enhancement(原创)

发表于: 2013-10-28   作者:czmmiao   来源:转载   浏览次数:
摘要: OLTP Table CompressionIn earlier releases, you could compress data only during bulk load operations such as during a direct load or a create table as select operation. You couldn’t, however, compress

OLTP Table Compression
In earlier releases, you could compress data only during bulk load operations such as during a direct load or a create table as select operation. You couldn’t, however, compress data during a DML operation such as an insert operation. Oracle Database 11g extends its table compression capability to OLTP workloads, meaning you can now compress data during a data insertion job, for example. The compression technology Oracle uses works independent of the application, meaning you can use compression for packaged applications such as SAP and PeopleSoft.
OLTP compression saves you storage by reducing space consumption by 50 to 75 percent. A major concern when compressing data is the impact on performance, especially during read operations, when the database usually has to uncompress the data before reading it. Oracle’s new OLTP compression technology doesn’t degrade write performance, while improving the read performance. Write performance doesn’t degrade because of Oracle’s batched compression strategy. The read performance is better because Oracle reads compressed data directly without first uncompressing the data.
When new data comes in, the database inserts that data into a data block, but in an uncompressed format. Once the block reaches its PCTFREE level, Oracle compresses the data in the block. This compression strategy is efficient and also uses space efficiently by eliminating the holes made by the deleted data in the data blocks.
Setting Up Table Compression   

Table compression was introduced in Oracle 9i as a space saving feature for data warehousing projects. In 11g it is now considered a mainstream feature that is acceptable for OLTP databases. In addition to saving storage space, compression can result in increased I/O performance and reduced memory use in the buffer cache. These advantages do come at a cost, since compression incurs a CPU overhead, so it won't be of benefit to everyone.

Note. Basic table compression is a free feature of the Enterprise Edition database, but OLTP compression requires the Advanced Compression option.
The compression clause can be specified at the tablespace, table or partition level with the following options:

  • NOCOMPRESS - The table or partition is not compressed. This is the default action when no compression clause is specified.
  • COMPRESS - This option is considered suitable for data warehouse systems.Compression is enabled on the table or partition during direct-path inserts only.
  • COMPRESS FOR DIRECT_LOAD OPERATIONS - This option has the same affect as the simple COMPRESS keyword.
  • COMPRESS FOR ALL OPERATIONS - This option is considered suitable for OLTP systems. As the name implies, this option enables compression for all operations, including regular DML statements. This option requires the COMPATIBLE initialization parameter to be set to 11.1.0 or higher. In 11gR2 this option has been renamed to COMPRESS FOR OLTP and the original name has been deprecated.

The following examples show the various compression options applied at table and partition level.

-- Table compression.
CREATE TABLE test_tab_1 (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL
)
COMPRESS FOR ALL OPERATIONS;
-- Partition-level compression.
CREATE TABLE test_tab_2 (
  id            NUMBER(10)    NOT NULL,
  description   VARCHAR2(50)  NOT NULL,
  created_date  DATE          NOT NULL
)
PARTITION BY RANGE (created_date) (
  PARTITION test_tab_q1 VALUES LESS THAN (TO_DATE('01/01/2008', 'DD/MM/YYYY')) COMPRESS,
  PARTITION test_tab_q2 VALUES LESS THAN (TO_DATE('01/04/2008', 'DD/MM/YYYY')) COMPRESS FOR DIRECT_LOAD OPERATIONS,
  PARTITION test_tab_q3 VALUES LESS THAN (TO_DATE('01/07/2008', 'DD/MM/YYYY')) COMPRESS FOR ALL OPERATIONS,
  PARTITION test_tab_q4 VALUES LESS THAN (MAXVALUE) NOCOMPRESS
);
Table-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TABLES views.
SELECT table_name, compression, compress_for FROM user_tables;
TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
TEST_TAB_1                     ENABLED  FOR ALL OPERATIONS
TEST_TAB_2
2 rows selected.
Tables defined with partition-level compression and no table-level compression display NULL values in these columns.
Partition-level compression settings are reflected in the COMPRESSION and COMPRESS_FOR columns of the [DBA|ALL|USER]_TAB_PARTITIONS views.
SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions;
TABLE_NAME                     PARTITION_NAME                 COMPRESS COMPRESS_FOR
------------------------------ ------------------------------ -------- ------------------
TEST_TAB_2                     TEST_TAB_Q1                    ENABLED  DIRECT LOAD ONLY
TEST_TAB_2                     TEST_TAB_Q2                    ENABLED  DIRECT LOAD ONLY
TEST_TAB_2                     TEST_TAB_Q3                    ENABLED  FOR ALL OPERATIONS
TEST_TAB_2                     TEST_TAB_Q4                    DISABLED
4 rows selected.
The compression settings for tables and partitions can be modified using the ALTER TABLE command. The alterations have no effect on existing data, only on new operations applied to the table.
ALTER TABLE test_tab_1 NOCOMPRESS;
ALTER TABLE test_tab_2 MODIFY PARTITION test_tab_q4 COMPRESS FOR ALL OPERATIONS;
Default compression settings can be specified at the tablespace level using the CREATE TABLESPACE and ALTER TABLESPACE commands. The current settings are displayed in the DEF_TAB_COMPRESSION and COMPRESS_FOR columns of the DBA_TABLESPACES view.
CREATE TABLESPACE test_ts
  DATAFILE '/u01/app/oracle/oradata/DB11G/test_ts01.dbf'
  SIZE 1M
  DEFAULT COMPRESS FOR ALL OPERATIONS;
SELECT def_tab_compression, compress_for
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_TS';
DEF_TAB_ COMPRESS_FOR
-------- ------------------
ENABLED  FOR ALL OPERATIONS
1 row selected.
ALTER TABLESPACE test_ts DEFAULT NOCOMPRESS;
SELECT def_tab_compression, compress_for
FROM   dba_tablespaces
WHERE  tablespace_name = 'TEST_TS';
DEF_TAB_ COMPRESS_FOR
-------- ------------------
DISABLED
1 row selected.
DROP TABLESPACE test_ts INCLUDING CONTENTS AND DATAFILES;
When compression is specified at multiple levels, the most specific setting is always used. As such, partition settings always override table settings, which always override tablespace settings.
The restrictions associated with table compression include:

  • Compressed tables can only have columns added or dropped if the COMPRESS FOR ALL OPERATIONS option was used.
  • Compressed tables must not have more than 255 columns.
  • Compression is not applied to lob segments.
  • Table compression is only valid for heap organized tables, not index organized tables.
  • The compression clause cannot be applied to hash or hash-list partitions. Instead, they must inherit their compression settings from the espace, table or partition settings.
  • Table compression cannot be specified for external or clustered tables.

参考至: 《McGraw.Hill.OCP.Oracle.Database.11g.New.Features.for.Administrators.Exam.Guide.Apr.2008》            http://www.oracle-base.com/articles/11g/table-compression-enhancements-11gr1.php
本文原创,转载请注明出处、作者

如有错误,欢迎指正

邮箱:czmcj@163.com

Oracle 11g Compress Enhancement(原创)

  • 0

    开心

    开心

  • 0

    板砖

    板砖

  • 0

    感动

    感动

  • 0

    有用

    有用

  • 0

    疑问

    疑问

  • 0

    难过

    难过

  • 0

    无聊

    无聊

  • 0

    震惊

    震惊

编辑推荐
Overview In Oracle Database 10g, Oracle introduced the new Transparent Data Encryption (TDE)
Automatic Diagnostic Repository The automatic diagnostic repository tracks all problems and i
较之前一版本,SQL Server 2005可以说是作出了根本性的革新。对于一般的编程人员来说,最具吸引力的
在第一部分中,我们讨论了APPLY和CTE这两个T-SQL Enhancement。APPLY实现了Table和TVF的Join,CTE通
Material Status Enhancement Introduction The material status feature was originally developed
Material Status Enhancement Introduction The material status feature was originally developed
Material Status Enhancement Introduction The material status feature was originally developed
原文地址:http://www.2cto.com/database/201208/150620.html 一、Oracle 下载 注意Oracle分成两个文
oracle 11g 装好以后好久木有动了,今天找健健来一起研究,真是好。 先把oracle服务打开,这家伙太
一、Oracle 下载 注意Oracle分成两个文件,下载完后,将两个文件解压到同一目录下即可。 路径名称中
版权所有 IT知识库 CopyRight © 2009-2015 IT知识库 IT610.com , All Rights Reserved. 京ICP备09083238号