The Difference between Online and Offline compression


Imagine you have two tables each one with 250,000 records and they just had been created with CTAS. One was created using regular compression and the other with Advanced Compression. Let’s check out many 8k data blocks each one has:

 

TABLE_NAME                     COMPRESS COMPRESS_FOR     BLOCKS
------------------------------ -------- ------------ ----------
TAB1                           ENABLED  BASIC              3584
TAB2                           ENABLED  OLTP               4096

 

TAB1 was compressed using the BASIC compression method and has 3585 blocks, whilst TAB2 has been compressed using the Advanced Compression algorithm (OLTP) and strangely has more blocks, 4096 in total.

 

What’s going on here?

 

Is Advanced Compression worst than regular compression? If so why Oracle charges extra for that feature?

 

I think the answer is in the future! Let’s insert another 250,000 in each table and see what will be the size in each one. I have a table called NPTAB with millions of records in which those two tables were based upon. So I’ll just go to that table and fetch another 250k records:

 

SQL> insert into rep.tab1 select * from rep.nptab where rownum < 250001;

250000 rows created.

SQL> c/tab1/tab2
  1* insert into rep.tab2 select * from rep.nptab where rownum < 250001
SQL> r
  1* insert into rep.tab2 select * from rep.nptab where rownum < 250001

250000 rows created.

SQL> commit;

Commit complete.

 

And now let’s check out how many blocks are there in each table:

 

TABLE_NAME                     COMPRESS COMPRESS_FOR     BLOCKS
------------------------------ -------- ------------ ----------
TAB1                           ENABLED  BASIC             16384
TAB2                           ENABLED  OLTP               9216

 

What the hell? The Advanced Compressed table is now almost 7 thousand blocks smaller than the TAB1 compressed with the basic method. What happened?

Easy! Advanced Compression is not just good compression, but it’s online compression. Data is compressed once and read many times. Data is compressed as it comes, it’s not a two time process, it’s all done at the same time. So when data is inserted compressed it already takes less space, whilst the other table (TAB1) had it’s data compressed when it was created, but when data came, everything was de-compressed.

 

Queries take less time because objects are smaller. Take a look of how much time it takes to count all the records:

 

SQL> select count(*) from rep.tab1;

  COUNT(*)
----------
    499999

Elapsed: 00:00:16.03

SQL> select count(*) from rep.tab2;

  COUNT(*)
----------
    499999

Elapsed: 00:00:07.45
 

The Advanced Compressed table takes 9 seconds less to scan!

Less space, less time to execute queries, wow! Is it all good? I’m inclined to say yes, but data insertion in Advanced Compressed table have a slight performance degradation. How much? Let’s truncate both tables and see how much time it will take to put all the 499999 records back in:

 

SQL> insert into rep.tab1 select * from rep.nptab where rownum < 500000;

499999 rows created.

Elapsed: 00:00:41.37

SQL> insert into rep.tab2 select * from rep.nptab where rownum < 500000;

499999 rows created.

Elapsed: 00:01:25.87

 

There is no perfect world, and so data insertion was slower 200%.

 

You decide what’s best for you: slower data insertion or less storage and far faster queries?

 

Take your pick.

 

LMC.

About these ads

One thought on “The Difference between Online and Offline compression

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s