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

How It Works
DBMS_COMPRESSION.GET_COMPRESSION_RATIO performs the following tasks:

• Samples data from the input table and creates a 1,000,001 row uncompressed table in your

scratch tablespace

• Samples the same input table and creates a 1,000,001 row compressed table with the HCC

compression flavor specified in the comptype argument

• Compares the storage results using several other temporary tables, which it drops at the

completion of the procedure

• Reports the estimated number of rows, blocks, and the compression ratio

Because the compression advisor samples a million rows from your source table, the estimated block counts
and compression ratio will likely be very accurate; the compression advisor performs an intermediate set of
uncompressed and compressed tables using PARALLEL CREATE TABLE AS ( PCTAS ).
In the solution of this recipe, we demonstrated using the compression advisor to estimate the HCC compression
statistics when compressing with Compress For Query Low . You can execute the DBMS_COMPRESSION procedure using
the following choices as well:

COMPTYPE=>DBMS_COMPRESSION.COMP_FOR_QUERY_HIGH = Query High •

COMPTYPE=>DBMS_COMPRESSION.COMP_FOR_ARCHIVE_LOW = Archive Low •

COMPTYPE=>DBMS_COMPRESSION.COMP_FOR_ARCHIVE_HIGH = Archive High

•

COMPTYPE=>DBMS_COMPRESSON.COMP_NOCOMPRESS = No Compression •

COMPTYPE=>DBMS_COMPRESSION.COMP_FOR_OLTP = OLTP compression. Note that this
compression flavor is not one of the HCC compression types, but rather a feature of Oracle
Advanced Compression.

•

16-2. Building HCC Tables and Partitions
Problem
You wish to build Hybrid Columnar Compressed tables or partitions in your Exadata database.

Solution
In this recipe, you will learn how to create HCC tables and partitions in your database using the COMPRESS FOR storage
clause in the CREATE statement as well as setting the default compression level for tablespaces.

Creating a HCC Table Compressed for Query High
Execute the following CREATE TABLE statement, with the HCC clauses in bold :

SQL> create table mytab_compqueryhigh
(empname varchar2(30)
,empno number
,empdescr varchar2(100)
,hire_date date)
tablespace apps_ts_tx_idx compress for query high ;
Table created.
SQL>