Advanced Compression in Oracle Database 11g

Oracle database release 11g introduced a new type of compression called ”Advanced Compression”. This option provides multiple features including OLTP table compression.

So, let’s test out this new feature.

First of all, we are going to create 3 new tables:

nocomp – table without compression

basiccomp – basic compression

oltpcomp – advanced compression

create table nocomp (a int, b char(30), c date);

create table basiccomp (a int, b char(30), c date) compress;

create table oltpcomp (a int, b char(30), c date) compress for oltp;

Now we check its creation.

select table_name,compression,compress_for from user_tables where table_name in ('NOCOMP','BASICCOMP', 'OLTPCOMP');

TABLE_NAME COMPRESS COMPRESS_FOR

NOCOMP DISABLED

BASICCOMP ENABLED BASIC

OLTPCOMP ENABLED OLTP

Next we add 500.000 rows with some kind of repetition so we can use the compression.

Previously to the inserts, we execute the followings commands so we can start checking the times for each operation plus some other execution information:

set timing on

set autotr on

Now the insertions:

insert into nocomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;

insert into basiccomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;

insert into oltpcomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;

Result Times:

nocomp = 11.29 s

basiccomp = 13.57 s

oltpcomp = 22.36 s

As we can see, insertions with OLTP compression are slower.

Also, we can see that the table with OLTP compression uses a lot more redo than the other two.

Redo size = 28M8 / 28M7 / 95M1

But, let’s see how the pure data compression go.

select segment_name,round(bytes/1024/1024,2) Mb from user_segments where segment_name in (‘NOCOMP’,’BASICCOMP’,’OLTPCOMP’);

SEGMENT_NAME MB

----------------------------------

NOCOMP 27

BASICCOMP 24

OLTPCOMP 8

As we can see the OLTP compression almost compressed the data by 1/3.

OLTP Compression is slower and uses more redo but its compression is quite impressive.

Next we are going to do the same experiments but this time with direct inserts.

insert /*+ APPEND */ into nocomp select rownum , chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;

insert /*+ APPEND */ into basiccomp select rownum, chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;

insert /*+ APPEND */ into oltpcomp select rownum ,chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 500000;

select segment_name,blocks,round(bytes/1024/1024,2) Mb from user_segments where segment_name in (‘NOCOMP’,’BASICCOMP’,’OLTPCOMP’);

Results for Time and Space:

nocomp = 11.23 s / 24 MB

basiccomp = 11.40 s / 7 MB

oltpcomp = 11.32 s / 7 MB

With direct inserts, the times for the insertions are almost the same, but the two tables with some kind of compression use equally 1/3 of the space.

Finally, we are going to test how the different tables behave respect update and select operations.

First though, we increase the number of inserted rows from 500.000 to 2 milions.

(delete the tables and create new ones)

insert into nocomp select rownum , chr(64+trunc(dbms_random.value(1,3))),trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 2000000;

insert into basiccomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 2000000;

insert into oltpcomp select rownum , chr(64+trunc(dbms_random.value(1,3))), trunc(sysdate)+trunc(dbms_random.value(1,10)) from dual connect by rownum <= 2000000;

Times for the insertions:

nocomp = 48 s

basiccomp = 51s

oltpcomp = 1 m 25 s

select segment_name,blocks,round(bytes/1024/1024,2) Mb from user_segments where segment_name in (‘NOCOMP’,’BASICCOMP’,’OLTPCOMP’);

Space used per table:

nocomp = 112 MB

basiccomp = 104 MB

oltpcomp = 31 MB

Once checek the time and space, we focus on the selects and updates:

select a from nocomp where b='A' and a>200000 and a<300000;

select a from basiccomp where b='A' and a>200000 and a<300000;

select a from oltpcomp where b='A' and a>200000 and a<300000;

Times:

nocomp = 22.28 s

basiccomp = 22.67 s

oltpcomp = 21.64 s

update nocomp set b='C' where b='B' and rownum <= 200000;

update basiccomp set b='C' where b='B' and rownum <= 200000;

update oltpcomp set b='C' where b='B' and rownum <= 200000;

Times:

nocomp = 5 s

basiccomp = 10 s

oltpcomp = 1 m 10 s

As we can see, both the inserts and especially the updates are slower with oltp compression but on the other hand, the selects take the same time and it uses much less space.

That's it. Thanks for reading.

(We might test LOBs compression in the near future.)