Table Compression

Now a days Most systems usually involve large amounts of data stored in a few very large tables. As these systems evolve, the demand on disk space will grow quickly. In today's environment, data warehouses of hundreds of terabytes have become increasingly common. To manage disk capacity, the table compression feature introduced in Oracle9i Release 2 can significantly reduce the amount of disk space used by database tables and improve query performance in some cases.

Working Mechanism

The Oracle9i Release 2 table compression feature works by eliminating duplicate data values found in database tables. Compression works at the database block level. When a table is defined as compressed, the database reserves space in each database block to store single copies of data that appear in multiple places within that block. This reserved space is called the symbol table. Data tagged for compression is stored only in the symbol table and not in the database rows themselves. As data tagged for compression appears in a database row, the row stores a pointer to the relevant data in the symbol table, instead of the data itself. The space savings come from eliminating redundant copies of data values in the table. The effects of table compression are transparent to a user or an application developer. Developers access a table the same way regardless of whether a table is compressed or not, so SQL queries don't have to change once you decide to compress a table. Table compression settings are usually configured and managed by database administrators or architects, with little involvement from developers or users.

Create a Compressed Table

To create a compressed table, use the COMPRESS keyword in the CREATE TABLE statement. The COMPRESS keyword directs Oracle Database to store rows in the table in compressed format wherever possible. The following is an example of the CREATE TABLE COMPRESS statement:

CREATE TABLE EMP (
ID NUMBER NOT NULL,
NAME VARCHAR2(50) NOT NULL,
SAL NUMBER NOT NULL,
JOIN_DATE DATE NOT NULL,
ADDRESS VARCHAR2(100)
) COMPRESS;

Alternatively, you can use the ALTER TABLE statement to change the compression attribute of an existing table, as in the following:

ALTER TABLE EMP COMPRESS;

To determine whether a table has been defined using COMPRESS, query the USER_TABLES data dictionary view and look at the COMPRESSION column, as in the example below:

SELECT TABLE_NAME, COMPRESSION FROM USER_TABLES WHERE TABLE_NAME='EMP';

TABLE_NAME COMPRESSION
------------------ -----------
EMP ENABLED


The COMPRESS attribute can also be defined at the tablespace level, either at the time of creation (by using CREATE TABLESPACE) or later (by using ALTER TABLESPACE). The COMPRESS attribute has inheritance properties similar to those of storage parameters. When a table is created in a tablespace, it inherits the COMPRESS attribute from the tablespace. To determine whether a tablespace is defined using COMPRESS, query the DBA_ TABLESPACES data dictionary view and look at the DEF_TAB_COMPRESSION column, as in the following example:

SELECT TABLESPACE_NAME,DEF_TAB_COMPRESSION FROM DBA_TABLESPACES;

TABLESPACE_NAME DEF_TAB_COMPRESSION
--------------- -------------------
DATA_TS_01 DISABLED
INDEX_TS_01 DISABLED

As you might expect, you can still explicitly compress or uncompress a table in a tablespace, regardless of the COMPRESS value at the tablespace level.

Loading Data into a Compressed Table

Note that when you specify COMPRESS as shown above, you aren't actually compressing any data. The commands above only modify a data dictionary setting. Data isn't actually compressed until you load or insert data into a table. Furthermore, to ensure that data is actually compressed, you need to use a proper method to load or insert data into the table. Data compression takes place only during a bulk load or bulk insert process, using one of the following four methods:

1. Direct path SQL*Loader
2. Serial INSERT with an APPEND hint
3. Parallel INSERT
4. CREATE TABLE ... AS SELECT


Note: If you don't use the correct loading or INSERT method, the data in the table will remain uncompressed, even if the table is defined using COMPRESS. For example, if you use conventional path SQL*Loader or regular INSERT statements, data will not be compressed.

When to Use Table Compression

The way that Oracle Database chooses to compress or not compress table data has implications for the kind of applications best suited for table compression. As described above, data in a table defined using COMPRESS gets compressed only if it is loaded using direct path mode or inserted using append or parallel mode. Data inserted through regular insert statements will remain uncompressed. In online transaction processing (OLTP) systems, data is usually inserted using regular inserts. As a result, these tables generally do not get much benefit from using table compression. Table compression works best on read-only tables that are loaded once but read many times. Furthermore, updating data in a compressed table may require rows to be uncompressed, which defeats the purpose of compression. As a result, tables that require frequent update operations are not suitable candidates for table compression.

Finally, consider the effects of row deletion on the use of table compression. When you delete a row in a compressed table, the database frees up the space occupied by the row in the database block. This free space can be reused by any future insert. However, since a row inserted in conventional mode isn't compressed, it is unlikely that it would fit in the space freed up by a compressed row. High volumes of successive DELETE and INSERT statements may cause fragmentation and waste even more space than would be saved using compression.


Compressing an Existing Uncompressed Table

ALTER TABLE EMP MOVE COMPRESS;

You can also use the ALTER TABLE ... MOVE statement to uncompress a table,

ALTER TABLE EMP MOVE NOCOMPRESS;

Note that the ALTER TABLE ... MOVE operation acquires an EXCLUSIVE lock on the table, which prevents any DML operation on the table while the statement executes. You can avoid this potential problem by using the online table redefinition feature of Oracle9i Database.


Compressing a Partitioned Table

ALTER TABLE EMP MOVE PARTITION EMP3_03 COMPRESS;

Benefits

The biggest reason to use table compression is to save storage space. A table in compressed form will usually occupy less space when compared to its uncompressed form. A compressed table can be stored in fewer blocks results in storage savings, but fewer blocks can mean performance improvements as well. Queries on a compressed table in an I/O bound environment will often complete more quickly, because they need to read fewer database blocks