Oracle Advanced Table Compression Option - Oracle 11g

With some data volumes doubling roughly every two to three years, mounting storage costs as well as application scalability and performance issues make data compression an attractive technique for organizations.Oracle Database 11g takes data compression to new heights with its Oracle Advanced Compression option, which, unlike the older table compression technology, you’ll have to separately license from Oracle.The Oracle Advanced Compression option includes new OLTP compression capabilities, but it goes way beyond mere table compression.The Oracle Advanced Compression option includes the following types of compression:

  • Compression of OLTP table data.
  • Compression of unstructured data with SecureFiles.
  • Compression of backup data produced by RMAN through the new fast RMAN compression technique.
  • Compression of the table data during a Data Pump export (in Oracle Data base 10g, you could compress only the metadata from an export job, not the table data itself).
  • Compression of redo data during its transmission to a standby database during redo gap resolution after a network or standby server outage.

Beginning with the Oracle 9i release,you could use table compression in an Oracle database.The table compression feature allowed you to specify that the database compress data while loading it through bulk load operations.These operations include the create table as select (CTAS) and direct path load techniques.However,the data compression capa bility was aimed at data ware housing environments only.In Oracle Data base 10g, you could compress a heap-organized table, but Oracle recommended compressing just read-only tables, which are mostly used in data warehouse settings, since the compression algorithm wasn’t optimized for normal Data Manipulation Language(DML) activity.Although you can issue DML commands against a compressed table, for per formance reasons Oracle recommended avoiding DML operations on the compressed tables.

The over head of compression in OLTP environment made the pre-Oracle Data base 11g compression techniques unsuitable for fast-paced OLTP-based data bases. In Oracle Database 11g, the table compression feature has been enhanced to support OLTP environments.Oracle uses new algorithms to provide a powerful new online transaction processing (OLTP)compression capability that allows the database to compress data during all types of DML activities, such as insert, delete, and update.The performance improvement during compression of data,which makes it app ealing for OLTP uses results from a deduction in the overhead of the writing of data during compression.Thus,compres sion is now a viable option for both OLTP and data warehouse environments.

How the New Compression Feature Works

During a block compression, Oracle eliminates all duplicate values within a block by storing only a single copy of the column value that has duplicates in a special table that holds compression metadata, called the symbol table.Instead of multiple duplicate values in a block, you then have several short references to those values stored in the symbol table.Oracle stores the symbol table that contains the metadata used to compress data within the data block itself.This local database symbol table usage is what offers you superior performance during OLTP data compression by severely reducing the I/O when the database accesses any of the compressed data.

Storage and Performance Gains

Oracle claims you’ll save your storage requirements by about two to three times (Oracle claims a compression ratio of 3.5:1) when you use the new table compression feature.Since Oracle can read the compressed data without having to uncompress the compressed data blocks, not only will there not be a per formance hit, which is what you expect during table compression, but there will be an improvement in performance.This reduction in I/O results from the reduced need for I/O, since Oracle needs to access fewer blocks of data when dealing with duplicated data.For the same reason, your buffer cache requirements would go down as well, since you can save more compressed data in the cache.

Although the new table compression feature doesn’t pose any additional burden on performance during reading the compressed data and can actually make for speedier query execution, writing compressed data is an altogether different proposition, because compressing data for writing to disk requires the database to perform additional work.Oracle has come up with a batch mode compression strategy, whereby the database doesn’t incur the overhead of compressing data each time it needs to write that data to disk. Rather, the database waits until the uncompressed data in a data block reaches a critical threshold to compress the entire components of the block.Oracle then allows more new data to be written to the block in which it is compressing the data, until the block can’t gain any more from further compression of the data in it.

Implementing OLTP Table Compression

You can specify table-level compression when you create a table,as shown here:

The compress for all operations clause specifies that the data basecompress all DML operations on the table comp_tbl,which happens to be a table used for DML operations.In addition,the compression applies to all bulk load (direct-path insert) operations,which are more common in a data ware house environment.The compress for all operations clause is new in Oracle Data base 11g and is designed for compression during DML operations in an OLTP environment. If, on the other hand,you were to specify table compression only for bulk load oper ations, as in Oracle Database 10g, specify the compress for direct_load operations option or simply the compress option.Thus, both the following statements will result in the compression of data during a direct-path load only:

You can also specify compression for an already existing table. However, the database will compress only the new data that is entered in the table. All the previously existing data will remain uncompressed.

Compressing Unstructured Data

The new Oracle SecureFiles feature offers an alternative to traditional Large Objects (LOBs) for storing unstructured data such as documents, XML files, and spreadsheets.The Advanced Compression option lets you reduce the storage needs for Secure Files data by the process of deduplication, which eliminates redundant copies of SecureFiles data.When you have multiple copies of the same document floating around your organization, the data base uses Secure Files deduplication to store only a single image of the SecureFiles data.The numerous copies of that document are replaced by pointers to the single image.If the company chairman sends a 10MB vision document to all 100,000 employees of the company, you’d normally need 1000GB of disk storage to store all 100,000 copies of the vision document.With Secure Files deduplication all you’ll need is merely 10MB, which is the size of a single document.The SecureFiles feature offers you reduced storage requirements and better performance.

Compressing Backup Data

RMAN provided backup compression in Oracle Database 10g. Although RMAN doesn’t need to uncompress the compressed data during a recovery, because of the extensive compression ratio, backup times are longer since the compres sion of data before backing it up imposes an overhead

The Oracle Advanced Compression option increases the performance of compressed RMAN backups while reducing the storage needs.The new ZLIB compression algorithm offered by RMAN in Oracle Database 11g reduces the compression ratio by 20 percent, and the compressed backups are up to 40 percent faster than those in the previous release of the database.

Compressing Data Pump Export Data

Oracle Database 10g Release 2 offered you the ability to compress a Data Pump export job’s metadata,but not the table data itself. In Oracle Database 11g, you can choose to compress the export data while performing the export.Although you can compress a normal export dump file with operating system utilities such as gzip,you’d uncompress it again before importing the data.With the new Data Pump data compression, you don’t have to uncompress the compressed dump file before importing data, since Data Pump import automatically uncompresses the compressed dump file during the import job.

Compressing Network Data

Network or standby server failures can prevent redo log data from being trans ported to a stand by server when you’re using Data Guard.Oracle will automatically synchronize the redo data between the primary and secondary servers after the resolution of the failure.This synchronization process, also called a redo gap resolution, can prove to be time-consuming if the database has to transmit a large amount of redo data over the network.Oracle Advanced Compression compresses the redo data that needs to be transmitted over the network,thus increasing the throughput of the redo gap resolution process.Oracle estimates that compression enhances high availability by helping synchronize the standby database up to two times faster with compressed redo data.For more on the compression of redo data during a redo gap resolution.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd Protection Status

Oracle 11g Topics