Hybrid Columnar Compression (HCC) in Oracle Exadata is a prime example of how physical infrastructure can significantly enhance the efficiency of data management and analysis\u2014when used correctly. This is an area that also affects developers and data engineers: what it is, how it works, and what to expect. If HCC is used in your project, then everyone on the team should be aware of it, including the specifics of what, where, and how it is implemented. Otherwise, there may be a few surprises, as in the example I encountered recently: instead of achieving the expected storage savings and improved performance, there was a huge increase in space consumption, along with negative performance effects. Read on to find out what happened.<\/p>\n\n\n\n\n\n\n\n
There are a couple of good sources to start with learning about Hybrid Columnar Compression. Just to name a few:<\/p>\n\n\n\n
I assume you already have a basic understanding of how HCC works. To follow, you should at least understand that multiple consecutive data blocks make up a Compression Unit (CU). Multiple rows are stored compressed in a CU and the storage is by column (column-major) in contrast to otherwise typical row-major storage in Oracle database. All columns belonging to one row are always completely contained in a single CU.<\/p>\n\n\n\n
Let’s first start with creating an uncompressed table using sample data set. <\/p>\n\n\n
\nSQL> create table hcc_test_raw nocompress as\nselect lpad(rownum,10, '0') id\n, lpad(dbms_random.value,1000, 'x') text\nfrom dual connect by level <= 100000\n\nTable created.\n\nSQL> select t.num_rows\n, t.avg_row_len\n, t.blocks\n, round(SUM(bytes)\/1024\/1024) AS actual_size_mb\n, sum(s.extents)\nfrom user_tables t\njoin user_segments s on s.segment_name = t.table_name\nwhere table_name = 'HCC_TEST_RAW'\ngroup by t.num_rows\n, t.avg_row_len\n, t.compress_for\n, t.blocks\n\n NUM_ROWS AVG_ROW_LEN BLOCKS ACTUAL_SIZE_MB SUM(S.EXTENTS)\n---------- ----------- ---------- -------------- --------------\n 100000 1012 14436 116 29\n1 row selected.\n\n<\/pre><\/div>\n\n\nListing 1: Introducing the test dataset and an uncompressed table for comparison<\/em><\/strong><\/p>\n\n\n\nNow let’s take the same data set but create the table using COMPRESS FOR QUERY HIGH<\/strong> this time (Listing 2).<\/p>\n\n\n\nSQL> create table hcc_test compress for query high as\nselect lpad(rownum,10, '0') id\n, lpad(dbms_random.value,1000, 'x') text\nfrom dual connect by level <= 100000\n\nTable created.\n\nSQL> select t.num_rows\n, t.avg_row_len\n, t.blocks\n, t.compress_for\n, round(SUM(bytes)\/1024\/1024) AS actual_size_mb\n, sum(s.extents)\nfrom user_tables t\njoin user_segments s on s.segment_name = t.table_name\nwhere table_name = 'HCC_TEST'\ngroup by t.num_rows\n, t.avg_row_len\n, t.compress_for\n, t.blocks\n\n NUM_ROWS AVG_ROW_LEN BLOCKS COMPRESS_FOR ACTUAL_SIZE_MB SUM(S.EXTENTS)\n---------- ----------- ---------- ------------ -------------- --------------\n 100000 1012 344 QUERY HIGH 4 1\n1 row selected.\n\n<\/pre><\/div>\n\n\nListing 2: The same data set in a table which is compressed FOR QUERY HIGH<\/em><\/strong><\/p>\n\n\n\n