Oracle basic compression is a free built-in feature, which can be used to reduce data size. This feature is only recommended for use with read-only data due to various limitations. In this blog post, I describe my own solution on how to use Oracle basic compression with a read-write aggregate table.

Oracle provides the use of basic compression for free, however this feature is quite limited. It allows you to compress data on an existing table or partition and also allows compressing data using direct-path inserts. One of the main disadvantages of using basic compression is regarding changes. Unfortunately basic compression was not designed to be used on tables and partitions which will have future DML & DDL. For such a scenario, Oracle Advanced Compression is most suitable. However for those who are unable to purchase this feature, I describe my own solution on how you could potentially use Basic compression on a partitioned aggregate table with future DML & DDL.

The Problem

Using basic compression with partitions is possible, however once compression is enabled the amount of restrictions that will exist on the table, makes future development very difficult:

• You are not able to set a default value for added columns
• You cannot drop columns
• Updates are not compressed and could cause the table size to exceed the original uncompressed table size.

My Solution

My solution tries to provide a method of applying basic compression, while still being able to use the table for future DML & DDL.

It incorporates a monthly aggregate table which will hold recent data, that is still potentially going to be updated. In addition we will create a new archive table, which will hold the compressed data. We will use a view in order to simplify the usage for any database users, as they can use the view in order to get all the data regardless if it has been archived. Lastly, we will also create a stored procedure which can be used to compress (archive) the data.

This solution can be easily modified to accommodate other scenarios such as daily partitioning etc. Please note that for best performance and ease of use, it is advisable to consider purchasing Oracle Advanced compression.

Create Aggregate Table

First create the example month aggregate table –

(press image for full SQL)

Create Archive Table

Create a table with a matching structure which will be used to store any compressed data –

Create View

Create a view which combines data from both tables.
This view will simplify the usage for any database users, as they can refer to the view to retrieve full data, regardless if it has been archived or not.

In addition, this view can be handy when you conduct future DDL on the aggregate table, which you do not want to implement on old archived data. For such as scenario you can add in the view, new hard-coded dummy values (‘N/A’) to represent the new columns in the archive table.

Create Archiving Procedure

Create a stored procedure which will be used to archive data (move data from one table to another and compress it).

(If needed, please see previous post about DIM_DATE)

Create Archiving Log

Create a table to log all details on executed archiving

Execute Solution

EXECUTE DATAMART.PRC_ARCHIVE_MONTH_PARTITION('DATAMART','DATAMART','FACT_SALES_MONTH','FACT_SALES_MONTH_ARCHIVE','201601','201601');

In the example above, we archive data from January 2016. This data is moved between DATAMART.FACT_SALES_MONTH and DATAMART.FACT_SALES_MONTH_ARCHIVE.

This solution compressed the data, and by storing the data in a separate table, it does not bring  DDL & DML into a halt.  Lastly, a view is created to simplify the usage, as if the data was actually stored in only one table.