Exadata Database Machine - “Hybride Columnar Compression(HCC)”

Hybrid Columnar Compression (HCC) allows the maximum levels of data compression by grouping column data. In turn cost-savings of enterprises for space, also enables performance improvements due to reduced I/O.

Types of Oracle Compression:

☑️Oracle Basic Compression

☑️Oracle Advanced Compression

☑️Hybrid Columnar Compression ( only Exadata )

✅ How to do Basic Table Compression ??

In basic compression we use the direct path method of injecting rows into the table. It basically bypasses the SQL engine and the transactional mechanism built into Oracle and inserts blocks above the segment’s high water mark or into an alternative “temporary” segment. More specifically, during the direct path insert, the buffer cache is not being utilized. After the direct path load operation finishes, it is mandatory to commit the transaction before anyone else can apply DML to the table. This is a benefit to the faster loading process.

✔ Oracle8i Database — introduced compression in indexes.

✔ Oracle9i Database Release 2 — new Table compression feature.

✔ It allows bulk load operations, useful in data warehouse environments.

Example 1:

SQL*Plus: Release 19.0.0.0.0 — Production on Fri Aug 5 11:19:45 2022
Version 19.3.0.0.0

Copyright © 1982, 2019, Oracle. All rights reserved.

Enter user-name: / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 — Production
Version 19.3.0.0.0

SQL> select name, currently_used from dba_feature_usage_statistics where lower(name) like ‘%compress%’;

NAME CURRE
— — — — — — — — — — — — — — — — — — — — — — — —
Backup ZLIB Compression FALSE
Backup BZIP2 Compression FALSE
Backup BASIC Compression FALSE
Backup LOW Compression FALSE
Backup MEDIUM Compression FALSE
Backup HIGH Compression FALSE
Oracle Advanced Network Compression Service FALSE
Segment Maintenance Online Compress FALSE
SecureFile Compression (user) FALSE
SecureFile Compression (system) FALSE
HeapCompression FALSE
Advanced Index Compression FALSE
Hybrid Columnar Compression FALSE
Hybrid Columnar Compression Conventional Load FALSE
Hybrid Columnar Compression Row Level Locking FALSE
Compression Advisor FALSE

16 rows selected.

SQL> select segment_name,bytes/1024/1024/1024 from dba_segments where bytes=(select max(bytes)from dba_segments);

SEGMENT_NAME BYTES/1024/1024/1024

— — — — — — — — — — — — — — — — — — — — — — — — — —TBL_ORD_DET 5.52148438

SQL> create table TBL_ORD_DET_TST1 compress basic as

2 select * from order.TBL_ORD_DET;

Table created.

SQL> select segment_name,bytes/1024/1024/1024 from dba_segments where segment_name=’TB_IN_TEST1';

SEGMENT_NAME BYTES/1024/1024/1024

— — — — — — — — — — — — — — — — — — — — — — — — — —

TBL_ORD_DET_TST1 .7421875

Example 2:

SQL> create table demo_basic compress for oltp

as select * from orders where 1=2;

SQL> alter table demo_basic nologging;

SQL> insert /*+ append */ into demo_basic select * from orders;

Elapsed : 00:00:24:20

✅ Oracle Advanced Compression:

A new feature in Oracle Database 11g Enterprise Edition allows to compress transaction-intensive online transaction processing (OLTP) databases requires a license for “Advanced Compression Option (ACO)”

(i) Basic table compression,

(ii) OLTP table compression, and

(iii) Index compression.

✔ Oracle advertise 2–4 x average storage saving

✔ Improved query performance for large amounts of data

✔ Compression enabled at partition or table level

✔ Block level compression

Example 3:

SQL> create table demo_oltp compress for oltp

as select * from orders where 1=2;

SQL> insert into demo_oltp as select * from orders;

30000000 rows created

Elaspsed: 00:03:11:40

SQL> commit;

Commit complete

✅ Hybrid Columnar Compression (Exadata):

Data is organised by columns instead of rows. Column organisation brings similar values together, enhancing compression 10x-15x average storage saving. Oracle claim up to 50x-70x savings in some cases.

With average data compression ratios of 10x from HCC, IT managers can drastically reduce, and often eliminate, their need to purchase new storage for several years.

For example, a 100-terabyte database achieving a 10x data compression ratio would utilize only 10 terabytes of physical storage. With 90 terabytes of storage now available, IT organizations could potentially delay storage purchases for a significant amount of time. The 90 terabytes of storage could be used to store up to 9 more databases with 100 terabytes of data compressed to 10 terabytes of actual disk space each.

Full support for use with …

• B-tree, Bitmap indexesm texr indexes
• MVIEWS
• Cell Offloading
• Partitioning
• Parallel Query
• Data Guard Physcial Standby Support, logical as of 11.2.0.2

☑️HCC — Data Warehouse and Archive options:

Hybrid Columnar Compression is an enabling technology for both HCC Warehouse Query Compression and HCC Archive Compression.We can implement HCC at the table, partition & tablespace level.

Get Cloud Evolving Data’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

✔10x average storage saving

✔10x scan I/O reduced

DW compression optimized for query performance, and is intended for application. Which result in reduced warehouse size with better performance. Online archival compression optimized for maximum compression and is intended for data that rearly changes. You can use HCC on all tables or with basic & OLTP compression by using partitationing.

Table compression methods used in Exadata

QUERY LOW compression, for DW with Load Time as a critical factor.

QUERY HIGH compression, for DW with focus on Space Saving.

ARCHIVE LOW compression, for Archival Data with Load Time as a critical factor.

ARCHIVE HIGH compression, for Archival Data with maximum Space Saving.

Examples of DW & Archive Compression:

1️⃣ DW with load time critical factor Fastest Load Time

SQL> create table demo_query_low compress for query low

as select * from orders where 1=2;

SQL> alter table demo_query_low nologging;

SQL> insert /*+ append */ into demo_query_low select * from orders;

Elapsed : 00:00:19:22

2️⃣ DW with focus on Space Saving

SQL> create table demo_query_high compress for query high

as select * from orders where 1=2;

SQL> alter table demo_query_high nologging;

SQL> insert /*+ append */ into demo_query_high select * from orders;

Elapsed : 00:00:27:80

3️⃣ Archival Data with Load Time Critical factor

SQL> create table demo_archive_low compress for archive low

as select * from orders where 1=2;

SQL> alter table demo_archive_low nologging;

SQL> insert /*+ append */ into demo_archive_low select * from orders;

Elapsed : 00:00:31:22

4️⃣ Archival Data with maximum space saving.

SQL> create table demo_archive_high compress for archive high

as select * from orders where 1=2;

SQL> alter table demo_archive_high nologging;

SQL> insert /*+ append */ into demo_archive_high select * from orders;

Elapsed : 00:03:57:70

☑️ Data Warehouse Compression

✅10x average storage savings

✅10x reduction in Scan IO

✅Optimized for speed

✅Example: create table emp (…) … compress for query [low | high];

☑️ Archive Compression

✅15x average storage savings, up to 70x on some data

✅ Greater access overhead

✅ For cold or historical data

✅ Optimized for space

✅ Reclaim Diasks. Keep Data Online

✅ Example à create table emp (…) … compress for archive [low | high];

In Hybrid Columnar compression, a set of rows grouped under Compression Unit based on the column data and get compressed.

For Ex. on the employee table, eventhough we have 1M records, the Department column might have a few unique values. So HCC will create a master data for the values and create only points to rest of the rows. Through this we can achieve 10x to 15x Compression ratio which will reduce the IO operations and Storage requirements.

☑️HCC Comparisons:

✅ Query Performance (fastest-> slowest)

✔ Query low, query high, archive low, no compress, archive high

✅ Best Compression rate (lowest -> highest)

✔ Query low, query high, archive low, archive high

✅ Data load from flat file (slowest -> fastest)

✔ Archive high, archive low, query high, query low, no compress)

☑️Compression Strategies:

✅ OLTP Applications

Table Partitioning

Heavily accessed Data

Partitions using OLTP Table Compression

Historical Data

Use Archive Compression

✅ Data Warehouses

Heavily Accessed Data

Partitions using Warehouse Compression

Historical Data

Use Archive Compression

☑️ Note:

Compression Advisor DBMS_COMPRESSION package provides compression advisor, which helps you to determine the expected compression ratio for aperticular table & a compression method.