Exploring Oracle Hybrid Columnar Compression (HCC): A Hands-On Lab with Real Results (Repost)!

Introduction:

Hybrid Columnar Compression (HCC) is a powerful feature in Oracle Database that significantly reduces storage footprint while optimizing performance for different workloads. In this blog post, will walk through a practical lab exercise that demonstrates how various HCC compression methods perform in terms of load time and storage efficiency.

Sales Table Setup:

We begin by using a sample table SALES with 20 million rows. Our goal is to test different compression methods and compare their performance and space savings.

1. OLTP Compression:

create table comp_oltp compress for oltp as select * from sales where 1=2;
insert into comp_oltp select * from sales;
commit;

Comment: OLTP compression is optimized for DML operations. It took 2 minutes and 16 seconds to load the data.

2. Query Low Compression:

create table comp_query_low compress for query low as select * from sales where 1=2;
alter table comp_query_low nologging;
insert /*+ append */ into comp_query_low select * from sales;
commit;

Comment: This method is designed for data warehousing queries. It loaded in just 27 seconds, showing excellent performance.

3. Query High Compression:

create table comp_query_high compress for query high as select * from sales where 1=2;
alter table comp_query_high nologging;
insert /*+ append */ into comp_query_high select * from sales;
commit;

Comment: Aimed at maximizing compression for query workloads. Load time was 34 seconds—slightly slower but still efficient.

4. Archive Low Compression:

create table comp_archive_low compress for archive low as select * from sales where 1=2;
alter table comp_archive_low nologging;
insert /*+ append */ into comp_archive_low select * from sales;
commit;

Comment: Suitable for infrequently accessed data. Load time was 35 seconds.

5. Archive High Compression:

create table comp_archive_high compress for archive high as select * from sales where 1=2;
alter table comp_archive_high nologging;
insert /*+ append */ into comp_archive_high select * from sales;
commit;

Comment: This method prioritizes maximum compression. As expected, it had the slowest load time at 4 minutes and 13 seconds, but the best compression ratio.

Results: Segment Size Comparison

select segment_name, bytes/1024/1024 as mb from user_segments order by 1;

Comment: The ARCHIVE HIGH method compressed 1.5 GB of data down to just 5 MB—a dramatic reduction!

Trade Off with Load Time and Conclusion

This lab clearly demonstrates the trade-offs between load time and compression ratio across different HCC methods. While ARCHIVE HIGH offers the best space savings, QUERY LOW and QUERY HIGH strike a great balance between performance and compression. HCC proves to be a valuable tool for optimizing storage in Oracle databases.