11g New Feature: OLTP Table Compression

In 11g table compression has been expanded and is now suitable for conventional
DML operations, including previously supported direct-load operations (e.g. INSERT /*+ APPEND */). Therefore, it is now possible to use compression in OLTP environments as well. It is important to note, however, that this feature requires compatible to be 11.1.0 or higher.

The benefit of using such compression is that disk space is greatly reduced as is memory consumption (since less buffers need to be written into an instance's buffer cache). The downside is that there is additional CPU overhead for loading as well as DML operations.

Here is an example of applying the new table compression to an existing table:

SQL> connect sh

Using SH.CUSTOMERS as the example, query its storage characteristics:

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "TABLE BLOCK COUNT"
  2  from sh.customers;

TABLE BLOCK COUNT
-----------------
             1454

SQL> select avg(count(*)) "AVERAGE RECORDS PER BLOCK"
  2  from sh.customers group by dbms_rowid.rowid_block_number(rowid);

AVERAGE RECORDS PER BLOCK
-------------------------
                38.170564

We now know how many blocks are employed by the table segment plus how many records are currently stored per block.

Create a copy of the table us CTAS and then observe whether the table compression feature stores the same number of records in fewer blocks or not.

SQL> create table sh.customers_compressed compress for all operations as select * from sh.customers;

Table created.

Query the data dictionary to confirm the newly created segments have the desired attribues:

SQL> select table_name, compression, compress_for
  2  from user_tables where table_name in ('CUSTOMERS','CUSTOMERS_COMPRESSED');

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
CUSTOMERS                      DISABLED
CUSTOMERS_COMPRESSED           ENABLED  FOR ALL OPERATIONS

Query the compressed table's storage characteristics:

SQL> select count (distinct dbms_rowid.rowid_block_number(rowid)) "TABLE BLOCK COUNT"
  2  from sh.customers_compressed;

TABLE BLOCK COUNT
-----------------
              763

SQL> select avg(count(*)) "AVERAGE RECORDS PER BLOCK"
  2  from sh.customers_compressed group by dbms_rowid.rowid_block_number(rowid);

AVERAGE RECORDS PER BLOCK
-------------------------
               72.7391874

The resulting compressed table stores the same data in about half the space:

SQL> select segment_name,  round(bytes/1024/1024) "MB"
  2  from dba_segments
  3  where owner='SH'
  4  and segment_name in ('CUSTOMERS','CUSTOMERS_COMPRESSED');

SEGMENT_NAME                           MB
------------------------------ ----------
CUSTOMERS_COMPRESSED                    7
CUSTOMERS                              12

Labels: 11g, Tuning