• Ask DB Specialists: Table Compression in Oracle
• Useful Oracle White Papers and Scripts
• Data Warehouse Best Practices
• It’s a Wrap!

You're invited to submit your Oracle-related questions to us at askdbspecialists12@dbspecialists.com. Please include your name and telephone number in your email.

This month’s question comes to us from Stephanie in Albuquerque, NM: How does Oracle's table compression feature work? In what situations should table compression be used, and how does it affect performance?

Hamid Minoui of the Database Specialists team responds: Oracle introduced the table compression feature in Oracle 9i Release 2. Table compression in Oracle is accomplished by eliminating duplicate values within a database block and replacing them with short references to entries in a symbol table. A symbol table, also known as a dictionary, is automatically created in each database block where data has been compressed.

This design makes each database block of a compressed table self-contained, meaning that all information needed to uncompress a database block is available within the block itself. Individual partitions of a partitioned table can be compressed, as can materialized views.

A symbol table is only created in database blocks that have been compressed, and not all blocks of a compressed table are necessarily compressed. Blocks that contain values with little repetition, or very short values, are not compressed by Oracle’s table compression algorithm.

For tables with a lot of duplicate values, Oracle’s table compression feature can dramatically reduce the size of the table segment. This can reduce disk space usage and buffer cache requirements, which in turn can reduce misses in the buffer cache. In many cases improvements in I/O efficiency are gained as a result of accessing considerably fewer database blocks for the same amount of data. Full table scans, exports, and database backups are among the operations that benefit the most from table compression.

There is a small CPU overhead involved in reconstructing the compressed blocks. However, the I/O performance gains typically more that make up for it.

Table compression is targeted primarily for data warehouse environments, where the tables are read-only or read-mostly. However, tables in others environments may benefit from compression as well.

Table compression is not appropriate for tables that participate in DML operations because of the overhead associated with them and limitations on the table compression feature. Only new data that is inserted into a table via a bulk load operation is eligible for compression. Bulk loads are done using SQL*Loader’s direct path load option, INSERT statements that use the APPEND hint, and the CREATE TABLE AS SELECT statement. Compression only applies to the new data being loaded—the compression status of existing database blocks remains unchanged.

Two ways to compress previously loaded data in existing tables are:

ALTER TABLE t1 MOVE COMPRESS;

CREATE TABLE t2 COMPRESS AS SELECT * FROM t1;

Oracle’s table compression feature can significantly reduce the amount of space needed to store certain types of data. This can lead to reduced disk storage and buffer cache sizing requirements, and improved performance for certain types of operations. Although primarily intended for data warehouse environments, table compression can be beneficial in many situations where data is usually loaded in bulk and used primarily read-only after that.

Useful Oracle White Papers and Scripts

We came across a resource recently that's worth taking a look at. Tim Gorman, a popular speaker and active participant in the Rocky Mountain Oracle Users Group, maintains an Oracle library with a collection of both presentations and scripts for your review and download. Some recent presentations and white papers at http://www.evdbt.com/papers.htm include:

• The Utility of Logon Triggers
• Materialized Views in Action
• Scaling to Infinity—Partitioning in Oracle Data Warehouses
• Supercharging STAR Transformations