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 数据库备份空间不足解决办法 Oracle 11g 数据库备份是数据库管理员的重要任务之一,而备份空间不足则是数据库管理员常遇到的问题之一。以下是关于解决 Oracle 11g 数据库备份空间不足方法的详细介绍。 ...
### 在SUSE下添加Oracle 11g自动备份 #### 背景介绍 随着企业对数据安全性的重视程度不断提高,对于关键业务系统的数据库备份变得尤为重要。Oracle 11g作为一款广泛使用的数据库管理系统,其备份策略对于保障数据的...
在Windows环境下,特别是Win2008或Win8操作系统中,管理和维护Oracle 11g数据库的备份至关重要。Oracle数据库的备份是确保数据安全性和业务连续性的重要环节。本资料"windows下oracle数据库备份压缩&删除历史备份....
### Oracle Database 11g OLTP压缩总结 #### 压缩概述 Oracle Database 11g Release 1 (11gR1) 引入了一项重要的新特性——OLTP(Online Transaction Processing)表压缩。这一功能允许数据库在执行常规的数据维护...
Oracle 10gR2 的压缩(Compress)技术是一种高效的数据存储策略,它能够显著减少数据库占用的物理存储空间,从而节省硬件成本并提高I/O性能。在Oracle数据库中,压缩可以应用于多个层次,包括表、表空间、物化视图、...
Oracle Database 11g Release 1 introduced the Advanced Compression Option to help customers cope with these challenges. Innovations in Oracle compression technologies help customers reduce the ...
### Oracle 10g 数据泵技术详解 #### 一、数据泵概述 Oracle 10g引入了数据泵(Data Pump)技术,这是一种用于高效迁移数据和元数据至其他数据库的强大工具。与之前的版本相比,数据泵提供了更快的数据传输速度,...
### Oracle基本建表语句知识点总结 #### 一、创建用户 在Oracle数据库中,创建用户是基础操作之一。这通常用于控制不同开发者或应用程序之间的访问权限。 **语法:** ```sql CREATE USER <username> IDENTIFIED BY...
### 高级压缩选项(ACO)与Oracle数据库11g #### 引言 随着企业数据量的急剧增长,管理这些数据变得越来越具有挑战性。这种数据爆炸的原因包括监管需求的变化、互联网上的多媒体内容传播以及Web 2.0带来的用户生成...
"HIS系统数据库Oracle7.3到Oracle10g的升级.pdf" 本文档主要介绍了HIS系统数据库从Oracle7.3到Oracle10g的升级过程,该升级旨在提高系统的效率和稳定性。下面是我们对该升级过程的总结: 1. 升级前的准备工作 在...
apache.commons.compress 第三方开源软件。能解压,压缩文件。里面包括commons-compress-1.9、commons-compress-1.2.1的版本。 当遇到这种错误,应该重点关注Caused by:后面的内容 Caused by:xxx Unsupported major....
Oracle P/L SQL实现文件压缩、解压功能,以下是此过程包的头部,包体经常打包处理plb,感兴趣用户可以下载下来。 Create or Replace Package UTL_ZIP AUTHID CURRENT_USER as Type File_List is Table of Clob; -...
今天,我们将讨论 Oracle 11g 数据库备份时存在磁盘空间不足解决方法的问题,并提供详细的解决步骤。 问题描述 在 Linux 操作系统下,备份 Oracle 11g 数据库表空间 jtkg 的结构和数据时,发现磁盘空间不足,无法...
赠送jar包:commons-compress-1.21.jar; 赠送原API文档:commons-compress-1.21-javadoc.jar; 赠送源代码:commons-compress-1.21-sources.jar; 赠送Maven依赖信息文件:commons-compress-1.21.pom; 包含翻译后...
赠送jar包:commons-compress-1.19.jar; 赠送原API文档:commons-compress-1.19-javadoc.jar; 赠送源代码:commons-compress-1.19-sources.jar; 赠送Maven依赖信息文件:commons-compress-1.19.pom; 包含翻译后...
`commons-compress.jar` 是一个Java库,由Apache软件基金会开发并维护,它提供了一种统一的接口来处理各种常见的压缩格式。这个库的核心在于它能够处理多种压缩和归档格式,如7z、ar、arj、bz2、cpio、dump、gz、jar...
KIC_Compress.KD
赠送jar包:commons-compress-1.4.1.jar; 赠送原API文档:commons-compress-1.4.1-javadoc.jar; 赠送源代码:commons-compress-1.4.1-sources.jar; 赠送Maven依赖信息文件:commons-compress-1.4.1.pom; 包含...
这些工具在Oracle 11g版本中得到了显著增强,特别是数据泵的引入极大地提高了卸库的速度。 #### 二、Oracle 11g 数据泵迁移步骤 在Oracle 11g中,数据泵是一种非常强大的工具,它能够有效地处理大量的数据迁移工作...
赠送jar包:commons-compress-1.20.jar; 赠送原API文档:commons-compress-1.20-javadoc.jar; 赠送源代码:commons-compress-1.20-sources.jar; 赠送Maven依赖信息文件:commons-compress-1.20.pom; 包含翻译后...