I will explain Hybrid Columnar compression ( HCC ) feature in Oracle Exadata in this article.
Read the first article of Hybrid Columnar Compression before this article with the following link.
Now let’s see HCC Compression with a real-time test. First of all, I create a table for testing purposes and insert about 160 million data.
create table msdeveci.hcc_test as select rownum as id, rownum + 1 as id2, 'Mehmet Salih Deveci Exadata Egitimi HCC Testleri' as name, mod(rownum,5) as id3, mod(rownum,10) as id4 , 14531453 as fiyat, trunc(sysdate - 100 + mod(rownum,10000)) as tarih, trunc(sysdate - 9999 + mod(rownum,10000)) as tarih2 from dual connect by level<=1e7;
From this test table, we will see the performance, compression and storage ratio by compressing it with Classic, OLTP and Exadata HCC methods respectively.
Exadata or Non Exadata in the environment of Oracle databases, we can compress the table with the following classic compression method.
160 million rows test table is inserted in 8:15 minutes into Classic compressed table.
With OLTP Compression method, compress as follows and perform the same insert operation.
160 million rows test table is inserted in 22:52minutes into OLTP compressed table.
Now let’s do the HCC method with Exadata. First let’s compress this with Query Low as follows.
160 million rows test table is inserted in 07:12 minutes into Query Low compressed table.
You can compress with Query High method like following.
160 million rows test table is inserted in 07:56minutes into Query High compressed table.
You can compress with Archive Low method like following.
160 million rows test table is inserted in 08:16 minutes into Archive Low compressed table.
You can compress with Archive High method like following.
160 million rows test table is inserted in 31:57 minutes into Archive High compressed table.
Let’s look at the segment size of these tables like following.
Although the Query High method seems to be compressed as much as Archive, it will make a difference in very large tables or different types of tables. The Archive method does 2-3 times more compression than the Query method, but is not as efficient as the Query method.
As a result, we have seen that when we compress a 16GB table with a classic method, it has compressed to 5500MB, which is about 3 times the compression.
However, when we used Exadata’s HCC, we saw that Query Low compressed up to 2500MB, which is about 6 times compression. At Query High, 16GB table is compressed to 500MB, which means an average of 32 times compression.
Now lets go to test for big table to see compression and performance clearly. Perform same compression like above.
All compression method are done like above. And compressed tables size are like following.
According to the results above, Archive High has compressed up to120 times, and Archive low has compressed up to 80 times. Compression details and OLTP times made to these tables can be found above.
As a result, If you want more performance during compression, choose HCC Query Low and Query High method. If you want more compression and storage saving during compression, then choose Archive High and Archive low method.