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