Oracle simple table compression

Oracle compression is a way of reducing the disk space needed to store data blocks. Oracle compression is included in Enterprise Edition only. Compression is achieved by replacing repeating values in each column within a data block with a code. For example if the following rows are stored in the same data block

the repeating values in the transaction_date, first_name and last_name columns can each be replaced by a numeric code. The data block will contain a code table which allows Oracle to look up the values corresponding to each code in order to compress and decompress the data.

Advantages and disadvantages of Oracle compression

The most obvious advantage of compression is the reduced disk space required to store the data. Compression ratios (the original data volume divided by the compressed data volume) of 2…4 are typical, although depending on the data the achieved compression can be much higher.

A beneficial side effect of the reduced disk space is faster I/O. Since less disk space is used to store the data, reading (and writing) is also faster, roughly by the same factor as the compression ratio.

Compressing and decompressing the data will cause a slight overhead as the code values need to be converted to actual values and vice versa, but in reality this overhead is negligible.

A drawback of simple table compression is that it works only with the following methods for inserting data into a table:

• Direct-path insert (e.g. INSERT /*+ APPEND */ INTO …)

• CREATE TABLE … AS SELECT…

• Direct path SQL Loader

Data inserted into a compressed table in any other way will not be compressed (this limitation does not exist with Advanced Compression introduced in Oracle 11g Release 2).

Table compression in practice

Compression can be specified when creating a new table. See the example ...



Source: http://www.experts-exchange.com/Database/Oracle/A_9091-How-to-use-Oracle-simple-table-compression.html  -- Source