Oracle Exadata Hybrid Columnar Compression (EHCC) Levels

SQL> connect sh/oracle@orcl
Connected.

Creating a HCC Table Compressed for Query High
==============================================
SQL> create table sales_queryhigh (
empname                 varchar2(30),
empno            number,
empdescr                varchar2(100),
hire_date               date
) tablespace users compress for query high;

Table created.

SQL> select table_name,compression,compress_for from user_tables where table_name='SALES_QUERYHIGH';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
SALES_QUERYHIGH                ENABLED  QUERY HIGH

Creating a HCC Table with HCC Partitions
========================================
SQL> create table orders (
  2  cid number,
  3  pid number,
  4  sid number,
  5  price number(5,2),
  6  discount number(3,2),
  7  odate date)
  8  partition by range (cid)
  9  (partition p1 values less than (100000) nocompress,
 10   partition p2 values less than (200000) compress for archive low,
 11   partition p3 values less than (300000) compress for query high,
 12   partition p4 values less than (maxvalue) compress for query low)
 13  enable row movement
 14  ;

Table created.

SQL> select table_name,compression,compress_for from user_tab_partitions where table_name='ORDERS';

TABLE_NAME                     COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
ORDERS                         DISABLED
ORDERS                         ENABLED  ARCHIVE LOW
ORDERS                         ENABLED  QUERY HIGH
ORDERS                         ENABLED  QUERY LOW

Compression attributes for Tablespace
=====================================

SQL> connect sys/oracle@orcl as sysdba
Connected.

SQL> create bigfile tablespace bigtbs
datafile '+DATA' size 1G
autoextend on next 1m
extent management local autoallocate
segment space management auto
default compress for query low;

Tablespace created.

SQL> select tablespace_name, def_tab_compression, nvl(compress_for,'NONE') compress_for
from dba_tablespaces;
where tablespace_name='BIGTBS';

TABLESPACE_NAME                DEF_TAB_ COMPRESS_FOR
------------------------------ -------- ------------
SYSTEM                         DISABLED NONE
SYSAUX                         DISABLED NONE
UNDOTBS1                       DISABLED NONE
TEMP                           DISABLED NONE
USERS                          DISABLED NONE
EXAMPLE                        DISABLED NONE
BIGTBS                         ENABLED  QUERY LOW

7 rows selected.

SQL>