HCC compression on Oracle Live SQL
I've written previously about
"Oracle Live SQL", a free service from Oracle where you can try your coding skills and learn using its vast code library. Today I want to mention one of its other features which may not be widely known: compression. If you use the service, you know that it provides only a few megabytes for your temporary schema which is usually enough for simple small tables and indexes to use in your tests. Also, they supply public schemas with some sample data. But what if you want to feed it more of your own data for your tests? You can actually use Oracle Compression for your objects. And you may be surprised to learn that you will have not only the expected basic and OLTP compression, but also a proper HCC compression. I think from that you can guess what kind on the backend you have for the service! Let's test and compare different types of conversions. As I've said, you don't have too much space in your schema for tests, hence you are limited to a small dataset. Let's create a table and check.
create table t1_nocompress as select * from
(select rownum t1_id, object_id p1, object_name p2, owner p3 from all_objects),(select rownum from dual CONNECT BY LEVEL <=2);
select segment_name,blocks,bytes/1024 bytes_k from user_segments;
From that I got an 8192 Kb segment which may not be sufficiently big to see a real difference in size for different compression types, but I'll try anyway. Let's try to create an object with advanced compression and see if it saves us any space.
drop table t1_nocompress purge;
create table t1_advcompress row store compress advanced as select * from
(select rownum t1_id, object_id p1, object_name p2, owner p3 from all_objects),(select rownum from dual CONNECT BY LEVEL <=2);
So, instead of 8192 Kb, we have 7168 Kb. The savings of 1M of space is not very impressive. What if we tried Hybrid Columnar Compression (HCC)?
drop table t1_advcompress purge;
create table t1_hccquerylow column store compress for query low as select * from
(select rownum t1_id, object_id p1, object_name p2, owner p3 from all_objects),(select rownum from dual CONNECT BY LEVEL <=2);
The segment size decreased to 2048 Kb or 4 times. That looks way better. What if we try the HCC with query high compression level?
drop table t1_hccquerylow purge;
create table t1_hccqueryhigh column store compress for query high as select * from
(select rownum t1_id, object_id p1, object_name p2, owner p3 from all_objects),(select rownum from dual CONNECT BY LEVEL <=2);
The segment was only 832 Kb which is almost a 10 times space reduction. As the result, we could create a table with 10 times more rows. The original table was created with 109054 rows.
drop table t1_hccqueryhigh purge;
create table t1_hccqueryhigh column store compress for query high as select * from
(select rownum t1_id, object_id p1, object_name p2, owner p3 from all_objects),(select rownum from dual CONNECT BY LEVEL <=20);
select count(*) from t1_hccqueryhigh;
Now we have a table with 1090540 rows and the segment size is still 8192 Kb. Hence, if you need a 1 million row table for your Oracle Live SQL test, HCC compression could be your savior. It is pretty good to have HCC compression and database in memory features on a free service for developers. It may help you to understand how it works and maybe test some of your scenarios. Happy coding everyone.
