Oracle Compressed Tables

Hi,

Did you know that Oracle supports compression in various forms in the database?  You can compress an entire tablespace (all of its contents), a table, or part of a table (at the partition level).  Using sub-partitioning, the sub-partitions can be either compressed or not compressed .

This quick blog hopes to introduce you to Table Compression, why and when you would use it, along with some useful syntax.

Compression is all about reducing the storage requirements for the data within the table.  The higher the compression, the greater the storage savings, and the longer it will take to uncompress (perhaps).

I’m a huge fan of compression.  Any computer’s longest task is physical I/O operations, so anything you can do to reduce physical I/O’s will more than likely increase query performance.

Oracle’s compression is designed so that you can leave frequently used data in an uncompressed format, somewhat older data in a compressed format, and infrequently accessed data in a tighter compression for additional savings in disk storage.  Why?  Maybe your shop has requirements to have two years of customer information available but most people are looking at the data on a quarter-by-quarter basis and seldom look in the prior year.  You can set up a compression scheme that allows for the data to be located on a cheaper/slower device in a highly compressed form.  The same SQL is used to access the data but it just might take a bit longer.  This is okay…you have met the requirements and saved space on your expensive RAID device.

There are two basic types of table compression: Basic Table Compression and Advanced Row Compression.  There a third type of compression useful for data warehouse applications: Hybrid Columnar Compression.

Basic Table Compression is designed for bulk load operations.  Oracle does not compress any data on the usual DML type operations (insert/update/delete).  It does compress data using the DIRECT PATH INSERT syntax/technology.  This includes direct path loading, create table as select statement, APPEND hint/syntax, and parallel inserts.

Advanced Row Compression DOES compress data using conventional SQL (DML) operations.  These include INSERT /UPDATE/DELETE operations as well as array inserts.

Both the basic and the advanced row compression compress the data with minimal CPU utilization and tend to reduce physical I/O operations.

Alter table emp ROW STORE COMPRESS BASIC; 

Basic Table Compression Example

Alter table emp ROW STORE COMPRESS ADVANCED;

**or**

Create table emp (columns and such)
NOCOMPRESS
Partition by range (hireyear)
   (partition Year2015 values less than (2016) ROW STORE COMPRESS ADVANCED );
   (partition Year2016 values less than (2017) ROW STORE COMPRESS BASIC ),
   (partition Year2017 values less than (MAXVALUE) );

Advanced Table Compression Example

In the above example, if compression is not specified in the partitions, the rows will not be compressed.

There are a couple more useful table compression implementations for Oracle and these are designed for data warehouse. 

Hybrid Columnar Compression comes in two flavors:  compress for archive and compress for query.  Each has two options: low and high.  Low is useful where there might be some DML operations such as additional data loads.  High is useful when no DML is expected at all. 

FOR ARCHIVE should be used when the data isn’t going to be accessed.  The HIGH feature will compress the data at the highest level and should be used on data that will be rarely accessed, if accessed at all. 

FOR QUERY is used for most data warehouse operations.  This allows for good query performance.  The LOW option might help with better load times.  The HIGH option might be useful if the data isn’t going to change much. 

Alter table emp COLUMN STORE COMPRESS FOR ARCHIVE HIGH;

Hybrid Table Compression Example using the maximum compression

Alter table emp COLUMN STORE COMPRESS FOR QUERY LOW;

Hybrid Table Compression Example for query access with data loads

Just like the table compression, these compression methods can be mixed for best results at the partition and subpartition levels.

Use this SQL to see what, if any, compression levels are in use on your objects.

Select TABLE_NAME, COMPRESSION, COMPRESS_FOR from USER_TABLES;

For partitioned objects, use this syntax:

Select TABLE_NAME, PARTITION_NAME, COMPRESSION, COMPRESS_FOR from USER_TAB_PARTITIONS;

Want to change the compression scheme?  Use the ALTER TABLE …MOVE ONLINE or MOVE PARTITION ONLINE…with different compression syntax. 

You can also see the level of compression at the row level using the function DBMS_COMPRESSION.GET_COMPRESSION_TYPE.  Use Toad and do a describe on this function. 

A value of:     

I hope you find this information useful in your day-to-day use of the Oracle RDBMS.

Dan Hotka

Author/Instructor/Oracle Expert

www.DanHotka.com

Dan@DanHotka.com