Exadata has columnar compression which is a very different from the regular table compression available on non Exadata Oracle databases.

While normal compression algorithms (basic, OLTP etc.) are all block based, columnar compression is Exadata specific and is based on column instead of blocks.

Here I want to show compression efficiency and time needed to compress large table.


1.
--no compression 
create table exacloud.tno_compress 
nologging
tablespace exacloud_tbs2
as
select * from exacloud.ex_tran_data_history where location = 1;
27,1 sec 

select bytes/1024/1024 as mb from dba_segments where owner = 'EXACLOUD' and segment_name = 'TNO_COMPRESS'; 
1280 MB


2.
--OLTP compression 
create table exacloud.toltp_compress 
nologging
compress for oltp 
tablespace exacloud_tbs2
as
select * from exacloud.ex_tran_data_history where location = 1;
50,6 sec  
 
select bytes/1024/1024 as mb from dba_segments where owner = 'EXACLOUD' and segment_name = 'TOLTP_COMPRESS'; 
480 MB

3.
--COLUMNAR compression 
create table exacloud.tcolumnar_compress 
nologging
compress for query high
tablespace exacloud_tbs2
as
select * from exacloud.ex_tran_data_history where location = 1;
55,5 sec 

select bytes/1024/1024 as mb from dba_segments where owner = 'EXACLOUD' and segment_name = 'TCOLUMNAR_COMPRESS'; 
88 MB

As expected, table without compression has the best creation time (case 1) of 27,1 sec at expense of storage – 1280 MB.

Table compressed for OLTP (case 2) is on the second place with creation time of 50,6 sec (almost 2 times longer than case 1 where there is no compression at all), while the segment space usage is 480 MB. This is almost 2,5 times less space needed comparing it with case 1 where the table is not compressed.

On the last position is columnar compression, with elapsed time for table creation of 55,5 sec, which is 5,5 sec more than the case 2.
But look at the space consumption. It’s spectacular, as space consumption is more than 6 times less when comparing with the case 2 (OLTP compression) and almost 15 times less than table without any compression at all.

You have to be aware of limitations of the columnar compression option like indexes or LOB’s won’t be compressed etc.

As I mention in the last article about Exadata, columnar compression is especially useful for Data Warehouse use cases.


Comments

There are no comments yet. Why not start the discussion?

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.