How to Compress Data in Oracle 12c

By Chris Ruel, Michael Wessler

Database Administrators (DBAs) spend a lot of their time and effort compressing data for retention and archiving. In Oracle 12c databases, compression is the act of taking data in your database and applying processes that reduce its storage footprint.

Because each year that goes by results in more and more data in your database, without specific processes in place, a database can quickly grow out of control, consuming all sorts of resources and hindering performance. As a first-class database, Oracle gives the DBA compression tools to make the jobs of removing data (purging) and keeping data (retention and archiving) quicker and more efficient.

How to use basic compression in Oracle 12c

Basic compression in the database has been around since Oracle 9i. It is called basic compression because it is very simple in what it does. However, it is also limited in its uses. Basic compression works much like using a zipping tool to compress a file on your operating system. In the database, objects can be compressed.

Using basic compression to reduce your table sizes can afford you efficiencies in using of space (obviously), CPU (table scanning), and memory (buffer cache usage). The greater efficiency means that tablespaces and backups take up less space. Backups run more quickly as well.

Be aware, though, that basic compression is primarily recommended for objects with a low frequency of data manipulation language (DML). It is preferred that the objects are practically read only. You can say practically because some data isn’t 100 percent black and white.

For example, a person’s name rarely changes, so a list of employee names is practically read only. A change may occur once in a while, but, it so infrequently that compressing the data and incurring the overhead of a change is negligible.

Think of it this way, if you want to edit a compressed file on your OS, what do you have to do first? You have to uncompress it. The same is true with Oracle basic compression. DML on compressed files suffers in terms of performance.

Here’s a compression example.

  1. Log in to SQL*Plus as the user HR and make a copy of the demo table employees called emp by typing

    <create table emp as select * from employees;>
  2. Check the size of this table by typing

    < select segment_name, bytes
    from user_segments
    where segment_name = 'EMP';>

    You see something like this:

    SEGMENT_NAME         BYTES
    -------------- --------------------
    EMP             65,536
  3. Insert rows into emp by running the following statement until you see “109568 rows created”:

    < insert into emp select * from emp;>
  4. Check the size of the emp table again by typing

    < select segment_name, bytes
    from user_segments
    where segment_name = 'EMP';>

    You see something like this:

    SEGMENT_NAME         BYTES
    -------------- --------------------
    EMP           18,874,368
  5. Compress the rows by typing

    <alter table emp move compress;>

    You see this:

    Table altered.
  6. Check the size one more time by typing

    < select segment_name, bytes
    from user_segments
    where segment_name = 'EMP';>

    You see something like this:

SEGMENT_NAME         BYTES
-------------- --------------------
EMP            9,437,184

As you can see, the table has reduced to about 50 percent of the original size. Depending on the type and organization of your data, you may see varying degrees of compression.

To see your tables compression details in the data dictionary, type

<select table_name, COMPRESSION, COMPRESS_FOR
from user_tables
where table_name = 'EMP';>

You see something like this:

TABLE_NAME           COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
EMP              ENABLED BASIC

To remove compression from a table in case you need to update a lot of data, type

<alter table emp move nocompress;>

You see this:

Table altered.

How to use advanced compression in Oracle 12c

The primary thing to understand about advanced compression is it uses more highly developed compression algorithms and data access policies so that the overhead of issuing DML against your compressed objects is all but eliminated. Well, the other important thing you need to be aware of is that advanced compression is a licensed feature.

Yes, that means you must pay extra for it. Your Oracle sales rep can help you figure out what it will cost. One thing you should consider, though, is the return on investment you can get from advanced compression. You’ll require less storage, and many operations will be improved. Calculating these returns will be an important part of your decision process.

Using advanced compression is very simple. If you followed the prior demo, you have an emp table in noncompressed format. You use that emp table in the following steps to apply advanced compression:

  1. To compress your emp table with advanced compression, type

    <alter table emp move compress for all operations;>

    You see something like this:

    Table altered.
  2. To check the new size of your emp table, type

    <select segment_name, bytes
    from user_segments
    where segment_name = 'EMP';>

    You see something like this:

    SEGMENT_NAME         BYTES
    -------------- --------------------
    EMP            9,437,184
  3. Check the compression details in the data dictionary by typing

    <select table_name, COMPRESSION, COMPRESS_FOR
    from user_tables
    where table_name = 'EMP';>

    You see something like this:

    TABLE_NAME           COMPRESS COMPRESS_FOR
    ------------------------------ -------- ------------
    EMP              ENABLED ADVANCED.

    Your table is enabled for all operations including DML with minimal performance loss.

You may have noticed in the examples for both basic and advanced compression the keyword MOVE is used in the commands. If you don’t use the keyword MOVE, the table is not compressed, but all future data inserted into the table will be compressed.

If you use the keyword MOVE and there are indexes on the table, those indexes will become corrupt. This corruption occurs because you’re changing the row location in the table when you proactively compress the data.

To fix this problem, after a MOVE compression action, rebuild the indexes. This is one reason you may choose to compress the data for future operations now and then move it later when you can incur downtime to rebuild the indexes.