11g Data Table Compression

Next to be covered is the impact of using compression in conjunction with DML operations on an OLTP table that might be found in a schema used to support order management.  In order to perform an impact analysis, two versions of the table order_lines are defined, one without compression and one with compression for all DML.

create table
   order_lines_uncompressed (
   line_id number primary key,
   header_id number not null,
   customer_id number,
   customer_name varchar2(30),
   product_id number,
   quantity number,
   price number
);

create table
order_lines_compressed (
   line_id number primary key,
   header_id number not null,
   customer_id number,
   customer_name varchar2(30),
   product_id number,
   quantity number,
   price number
)
compress for all operations; 

The data dictionary view dba_tables in 11g has an additional column, compress_for, to show the type of compression on a table.  The query to show this information for the newly created tables is:

SELECT
   table_name, compression, compress_for
FROM
   dba_tables
where
   table_name in ('ORDER_LINES_UNCOMPRESSED', 'ORDER_LINES_COMPRESSED'); 

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------
ORDER_LINES_UNCOMPRESSED       DISABLED
ORDER_LINES_COMPRESSED         ENABLED  FOR ALL OPERATIONS

After both tables are created, a new session must be opened and a block of PL/SQL code should be run to insert 100,000 rows into each of the new tables.  Since this is a simulation of an OLTP environment, a commit is issued after each insertion:

SQL> DECLARE
  2     i NUMBER := 1;
  3  BEGIN
  4  LOOP
  5   insert into ORDER_LINES_UNCOMPRESSED
  6   VALUES (i,i,12345,'TEST CUSTOMER',123,1,100);
  7   COMMIT;
  8   i := i+1;
  9   EXIT WHEN i>100000;
 10  END LOOP;
 11  END;
 12  / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:24.23 

SQL> DECLARE
  2     i NUMBER := 1;
  3  BEGIN
  4  LOOP
  5   insert into ORDER_LINES_COMPRESSED
  6   VALUES (i,i,12345,'TEST CUSTOMER',123,1,100);
  7   COMMIT;
  8   i := i+1;
  9   EXIT WHEN i>100000;
 10  END LOOP;
 11  END;
 12  / 

PL/SQL procedure successfully completed.

Elapsed: 00:00:29.39 

Notice that the inserts into the compressed table take slightly longer than the uncompressed table.  This is due to a slight CPU overhead for maintaining compression during DML operations. This occurs as the blocks reach an internally controlled threshold.  A comparison of the SQL Trace from each of these PL/SQL blocks can offer an explanation. These components show that the inserts to the compressed table consumed slightly more CPU and contained additional I/O wait events that totaled less than - of a second.  The overhead for maintaining compression for all DML appears to be minimal.  This will most likely prove acceptable for most database requirements, given the advantages that compression offers. 

The most obvious benefit of compression is the reduction of disk space consumption.  The following query shows that the compressed version of the table from the example above consumes substantially less disk space than the uncompressed version:

SQL> select
  2     segment_name, blocks, bytes
  3  from
  4     dba_segments
  5  where
  6     segment_name in ('ORDER_LINES_UNCOMPRESSED','ORDER_LINES_COMPRESSED');

SEGMENT_NAME                  BLOCKS      BYTES
------------------------- ---------- ----------
ORDER_LINES_COMPRESSED           384    3145728
ORDER_LINES_UNCOMPRESSED         640    5242880

In addition to saving disk storage, the more important benefits of compression are arguably the extra savings in I/O and cache efficiency.  Oracle operates directly on the compressed data without incurring the overhead required to uncompress the data.  Since operations on the compressed version of the table must scan fewer blocks, physical I/O is reduced.  The performance of table scans is then more efficient.  By using the autotrace feature, the improvement can be demonstrated between querying the compressed and uncompressed versions of the table.  The following query uses the uncompressed version and requires 627 consistent gets:

SQL> set autotrace traceonly

SQL> select
  2     *
  3  from
  4     order_lines_uncompressed
  5  where
  6     header_id = 50000;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        627  consistent gets
          0  physical reads
          0  redo size

The same query on the compressed version of the table uses 317 consistent gets:

SQL> select
  2     *
  3  from
  4     order_lines_compressed
  5  where
  6     header_id = 50000;

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        317  consistent gets
          0  physical reads
          0  redo size

Further testing shows similar savings in I/O for update and delete operations. 

The new feature for enabling table compression for all DML operations extends the use of table compression to OLTP environments.  While Oracle's compression algorithm does incur a slight CPU overhead when writing data, the benefits of compression are substantial and include a savings in disk storage, I/O, and cache efficiency.  Furthermore, the ease of altering between the compression methods mitigates the risk of changing the compression method.  Considering the significant savings with the use of compression, at the minimal cost of a small performance overhead when inserting the data, this new feature is ideal for OLTP tables. This feature is a major advancement in the use of compression and one of the most exciting features of Oracle 11g.