Space Saved With Exadata Hybrid Columnar Compression

I’ve been working with Exadata lately so I thought I’d share some numbers on HCC and Advanced Compression using ALL_OBJECTS.  This is a virtualized environment so I don’t have any real performance numbers to post, but the compression ratio is still impressive.  Most people think of compression in terms of saving space, but it also has a performance benefit for large table scans since it reduces physical IO.

create table all_objs as select a.* from all_objects a;

create table all_objs_compressed_oltp compress for all operations as select a.* from all_objects a;

create table all_objs_compressed_query compress for query as select a.* from all_objects a;

create table all_objs_compressed_archive compress for archive as select a.* from all_objects a;


select segment_name,round(bytes/1024/1024,3) size_mb
  from user_segments 
 where segment_name like 'ALL_OBJS%'
 order by 2 desc nulls last;

SEGMENT_NAME                 SIZE_MB                
---------------------------- ---------------------- 
ALL_OBJS                     7                      
ALL_OBJS_COMPRESSED_OLTP     2                      
ALL_OBJS_COMPRESSED_QUERY    0.375                  
ALL_OBJS_COMPRESSED_ARCHIVE  0.375 

So, for Advanced / OLTP Compression (compress for all operations) we achieve a compression factor of 3.5x. For HCC our compression factor jumps to 18.7x! Extrapolating a bit, a 1 TB table becomes a 55 GB table! Not sure why the compression ratio for Query and Archive is the same…