---------------------------
Oracle Database 9i introduced Table Compression several years ago to allow data to be compressed
during bulk load operations such as direct path load, CREATE TABLE AS SELECT…. (CTAS), etc.
This form of compression was ideally suited for data warehousing environments where most data is
loaded in the database using batch processes. Oracle Database 11g introduces a new feature called
OLTP Table Compression that allows data to be compressed during all types of data manipulation
operations, including conventional DML such as INSERT and UPDATE. In addition, the new feature
significantly improves performance by reducing the overhead of write operations making it suitable
for transactional or OLTP environments as well
It may be noted that Table Compression feature introduced in Oracle Database 9i is a base feature
of Enterprise Edition (EE) and continues to be so even in Oracle Database 11g. The new OLTP Table
Compression feature, however, is a part of the Oracle Advanced Compression
Performance
1) Table Scan Performance 2.5x Faster
2) 3% Storage Saving
3) 3% Fast DML Operation
CREATE TABLE emp (
emp_id NUMBER
, first_name VARCHAR2(128)
, last_name VARCHAR2(128)
) COMPRESS FOR ALL OPERATIONS;
Compression for Unstructured Data
SecureFiles, a new feature in Oracle Database 11g,for storing unstructured content, such as documents, spreadsheets and XML files which is a storage format for large object (LOB) data types to improve performance, reduce space usage,and enhance security.
SecureFiles Deduplication is an intelligent technology that eliminates duplicate copies of SecureFiles data.Consider an email application where 10 users receive an email with the same 1MB attachment. Without SecureFiles Deduplication, the system would store one copy of the file for each of the 10 users – requiring 10MB of storage. If the email application in ourexample had used SecureFiles with Deduplication, it would have stored the 1MB attachment just once. That’s a 90% savings in storage requirements. In addition to the storage savings, SecureFiles Deduplication also increases application performance. Specifically, write and copy operations are much more efficient since only references to the SecureFiles image
are written. Further, read operations may improve if duplicate SecureFiles data already exists in the buffer cache.
Deduplication can be enabled for SecureFiles as below:
CREATE TABLE images (image_id NUMBER,
image BLOB)
LOB(image) STORE AS SECUREFILE
(TABLESPACE lob_tbs DEDUPLICATE);
SecureFiles Compression
CREATE TABLE images (image_id NUMBER,
image BLOB)
LOB(image)STORE AS SECUREFILE
(TABLESPACE lob_tbs COMPRESS);
Different create statement for Table creation with LOB datatype
CREATE TABLEdoc_tab (pkey number(10) not null, document clob)
CREATE TABLEdoc_tab (pkey number(10) not null, document clob)
LOB (document) STORE AS ( TABLESPACE TEST )
create table doc_tab (pkey number(10) not null, document clob)
lob(document) store as doc_tab_document_lobseg
(nocache logging retention);
/* Enabling Secure File ,Compress, Deduplicate */
create table doc_tab (pkey number(10) not null, document clob)
lob(document) store as SECUREFILE doc_tab_document_lobseg_sf
(nocache logging retention auto COMPRESS DEDUPLICATE);
/* Enabling Secure File ,Compress, Deduplicate,Encryption */
create table doc_tab (pkey number(10) not null,document clob)
lob(document) store as SECUREFILE doc_tab_document_lobseg
(nocachelogging retention auto COMPRESS DEDUPLICATE ENCRYPT);
Compression for Backup Data
How to Enable Data Pump Compression
Users have the following options to determine which parts of a dump file set should be compressed:
• ALL enables compression for the entire export operation.
• DATA-ONLY results in all data being written to the dump file in compressed format.
• METADATA-ONLY results in all metadata being written to the dump file in compressed format.
This is the default.
• NONE disables compression for the entire export operation.
expdp hr FULL=y DUMPFILE=dpump_dir:full.dmp COMPRESS;
Recovery Manager Compression
Oracle Advanced Compression introduces new RMAN Compression capabilities that improve RMAN performance while still drastically reducing the storage requirements for backups. Based on the industry standard ZLIB compression algorithm, RMAN compressed backups are up to 40% faster than compressed backups in Oracle Database 10g.
How to Enable RMAN Compression
Syntax for Fast RMAN compression is as below:
RMAN> CONFIGURE COMPRESSION ALGORITHM ‘zlib’;
RMAN compression can be done as shown below:
RMAN> backup as COMPRESSED BACKUPSET database archivelog all;
--------------
