Oracle Exadata Database Machine: Exadata Hybrid Columnar Compression (EHCC)

Oracle Exadata Database Machine: Exadata Hybrid Columnar Compression (EHCC)

Introduction:

Exadata Storage Server includes Exadata Hybrid Columnar Compression feature. This option provides very high levels of data compression implemented inside the Exadata Storage Server.

This feature allows the database to reduce the number of physical I/Os required to scan a table.

You can specify Exadata Hybrid Columnar Compression (EHCC) at various levels

  • Table Level
  • Partition Level
  • Tablespace Level

You can also choose two types of Exadata Hybrid Columnar Compression (EHCC)

  • Warehouse Compression
    • ​This option is for optimizing Query performance.
    • Suitable for data warehouse applications.
    • Two options : Query High and Query Low
  • ​Online Archival Compression
    • ​This option is for optimized for maximum compression ratios.
    • Suitable for data changes very rarely.
    • Two options: Archive High and Archive Low

Table Compression Syntax:

  • Query Compression Syntax:
    • ​CREATE TABLE emp (…)  COMPRESS FOR QUERY [LOW | HIGH];
  • ​Archive Compression Syntax:
    • ​CREATE TABLE emp (…) COMPRESS FOR ARCHIVE [LOW | HIGH];

Data Organization: Exadata Hybrid Columnar Compression (EHCC)

  • Tables are organized into Compression Units (CU).
  • Size typically 32k (4 blocks * 8k block size)
  • Compression Units are larger than database blocks.
  • With Compression Units (CU), Data is organized by column not on row.
  • Logical Structure spanning multiple database blocks in the Database.
  • Each column is compressed separately.

Comparison Study of Compression: Query High Vs Archive High:

Compression MethodObject NameObject Size (MB)Object Creation  Time (HH:MM:SI)
No Compressionehcc_TEST25487.0625 
Query Highehcc_Qehcc128000:10:45.01
Archive Highehcc_Aehcc84800:40:32.26

Converting Compression to Non-Compression:

Object NameObject Size (MB)Object Conversion Time (HH:MM:SI)
ehcc_Qehcc2294200:03:08.42
ehcc_Aehcc2294700:02:57.58
Scenario Example: /*== Checking ehcc_TEST object size ==*/SQL> set pagesize 2000SQL> set long 10000SQL> set linesize 200SQL> set feedback onSQL> set echo onSQL> col segment_name format a18SQL> SELECT segment_name,sum(bytes)/1024/1024 Size_MBFROM user_segmentsWHERE segment_name in (‘ehcc_TEST’)GROUP BY segment_name;SEGMENT_NAME                     Size_MB————————————————————ehcc_TEST                                    25487.0625Elapsed: 00:00:00.59 /*== Creating Object with Query High Option ==*/SQL> CREATE TABLE ehcc_Qehcc COMPRESS FOR QUERY HIGH AS SELECT * FROM ehcc_TEST;Table created.Elapsed: 00:10:45.01 /*== Creating Object with Archive Option ==*/SQL> CREATE TABLE ehcc_Aehcc COMPRESS FOR ARCHIVE HIGH AS SELECT * FROM ehcc_TEST;Table created.Elapsed: 00:40:32.26 /*== Check the amount of space saving achieved, by viewing the segments ==*/SQL> SELECT segment_name,sum(bytes)/1024/1024 Size_MBFROM user_segmentsWHERE segment_name in (‘ehcc_Qehcc’,’ehcc_Aehcc’)GROUP BY segment_name;SEGMENT_NAME                            Size_MB————————————————————ehcc_Aehcc                                           848ehcc_Qehcc                                          1280Elapsed: 00:00:00.48 /*== View the COMPRESSION and COMPRESS_FOR columns in the USER_TABLES view ==*/SQL> set timing offSQL> set echo onSQL>select table_name,compression,compress_forfrom user_tableswhere table_name in  (‘ehcc_Aehcc’, ‘ehcc_Qehcc’, ‘ehcc_TEST’);TABLE_NAME                     COMPRESS        COMPRESS_FOR——————————        ——————      ————————-ehcc_Aehcc                            ENABLED           ARCHIVE HIGHehcc_Qehcc                            ENABLED           QUERY HIGHehcc_TEST                              DISABLED3 rows selected. /*== Converting from Compression to Non-Compression ==*/SQL> ALTER TABLE ehcc_Qehcc MOVE NOCOMPRESS PARALLEL;Table altered.Elapsed: 00:03:08.42SQL> ALTER TABLE ehcc_Aehcc MOVE NOCOMPRESS PARALLEL;Table altered.Elapsed: 00:02:57.58 /*== Checking size of the Objects after conversion ==*/SQL> SELECT segment_name,sum(bytes)/1024/1024 Size_MBFROM user_segmentsWHERE segment_name in (‘ehcc_Qehcc’,’ehcc_Aehcc’)GROUP BY segment_name;SEGMENT_NAME                           Size_MB————————————————————ehcc_Aehcc                                         22947ehcc_Qehcc                                         22942Elapsed: 00:00:00.53 /*== Checking Object count ==*/SQL>SELECT COUNT(*) FROM ehcc_TEST;  COUNT(*) —————- 102938023Elapsed: 00:00:07.11SQL> SELECT COUNT(*) FROM ehcc_Qehcc;  COUNT(*)—————-  102938023Elapsed: 00:00:06.75SQL> SELECT COUNT(*) FROM ehcc_Aehcc;  COUNT(*)—————-  102938023Elapsed: 00:00:06.72 Note: Applied Environmenta.    Oracle Enterprise Edition 11gR2 (11.2.0.3.0)b.    Oracle Exadata Quarter Rackc.    Oracle Linux 5.5 x86-64

Exadata Hybrid Columnar Compression (EHCC) Fully Supports:

  • B-Tree, Bitmap Indexes, Text indexes
  • Materialized Views
  • SmartScan Offload Operations
  • Partitioning
  • Parallel Query, PDML, PDDL
  • Schema Evolution Support, Online, Metadata-Only Add/Drop Columns
  • Data Guard Physical Standby Support

Exadata Hybrid Columnar Compression (EHCC) Fully Supports Storage Solutions:

  • Oracle Exadata
  • Oracle Exadata Expansion Rack
  • Pillar Axiom
  • ZFS Storage Appliance
  • SuperCluster

Exadata Hybrid Columnar Compression (EHCC) Benefit Across Stack:

  • Storage
  • Disk Bandwidth
  • Flash Cache
  • DRAM Cache
  • Test Environment, Development Environment & DR Environment
  • Database Backups

Exadata Hybrid Columnar Compression (EHCC) Estimation:

New Advisor in Oracle Database 11g Release 2

  • DBMS_COMPRESSION PL/SQL Package

Note: Before executing compression advisor create separate tablespace in database as it creates temporary tables and will do significant amount of I/O. Recommended time for compression advisor during less busy periods for the database.

Exadata Hybrid Columnar Compression (EHCC) Guidelines:

  • Exadata Hybrid Columnar Compression (EHCC) achieves its highest levels of data compression with data that is direct-path inserted.
  • Exadata Hybrid Columnar Compression (EHCC) is not recommended for tables that are modified most often in the database.
  • Exadata Hybrid Columnar Compression (EHCC) is not allowed on Index Organized Tables (IOT).
© 2021 www.oraworld-team.com | Designed & Developed by W3 Technologies