Hybrid Columnar Compression - Oracle Exadata Recipes: A Problem-Solution Approach - page 471

■ rows inserted via conventional insert or load operations to tablespaces with a default compression type set
will revert to uncompressed data for HCC compression. This is in contrast with OlTP or standard compression, in which
conventional inserts will result in rows compressed according to the tablespace's configuration parameters.

Note

How It Works
Creating HCC tables or partitions is achieved by using the COMPRESS FOR syntax in the storage clause of the SQL
statement. The following compression clauses are available for HCC:

•

COMPRESS FOR QUERY LOW

•

COMPRESS FOR QUERY HIGH

•

COMPRESS FOR ARCHIVE LOW

COMPRESS FOR ARCHIVE HIGH
The HCC-specific compression types are Query Low, Query High, Archive Low, and Archive High. The COMPRESS
FOR QUERY compression types are referred to as warehouse compression and the COMPRESS FOR ARCHIVE types are
called archive compression. Each flavor of HCC compression offers its own compression algorithm and is designed for
different business purposes. Depending on your data, Query Low offers the lowest compression ratio, followed by Query
High, then Archive Low, with Archive High yielding the best compression ratios. Along with this increased space savings
comes increased CPU usage when creating the compressed objects and an increasingly detrimental performance impact
should you need to modify data in your tables. Recipe 16-8 discusses the DML impact on HCC segments and Oracle's
documentation at docs.oracle.com/cd/E11882_01/server.112/e17120/tables 002.htm#CJAGFBFG , a good resource to
learn more about Oracle's HCC alternatives.

•

16-3. Contrasting Oracle Compression Types
Problem
You wish to measure the differences between HCC warehouse compression, HCC archive compression, and OLTP
compression, a compression solution offered by Advanced Compression in Oracle 11g.

Solution
In this recipe, you will learn how to use the compression advisor to compare the space savings differences and
compression ratios between the four different HCC compression types as well as OLTP compression. We will use a
test table created by using a row generator function to generate 200+ million rows and over 87,000 blocks based on the
DBA_OBJECTS view:

SQL> create table d14.myobj_uncomp
tablespace tbs_test
nologging as
select * from
(select * from dba_objects) obj,
(select rownum from dual connect by level<=1000)
/
Table created.
SQL> exec dbms_stats.gather_table_stats('D14','MYOBJ_UNCOMP');
PL/SQL procedure successfully completed.