Can Both HCC and Advanced Compression Be Used On The Same Table ?

- A non-partitioned table can only be compressed one way or the other and a partition can only be compressed in one way.

- But different partitions of the same table can have different compression attributes.

- So on Exadata a table may contain partitions that are compressed with EHCC and others with Advanced Compression Option.

- For example:

SQL> Create table mytest (

ord_day NUMBER(2),

ord_month NUMBER(2),

ord_year NUMBER(4),

ord_id NUMBER(10)

)

storage (initial 12k next 12k pctincrease 0 minextents 1)

PARTITION BY RANGE (ord_year,ord_month,ord_day)

(

PARTITION P1 VALUES LESS THAN (2001,3,31) organization hybrid columnar compress for query high ,

PARTITION P2 VALUES LESS THAN (2001,6,30) compress for oltp

)

/

SQL> set lines 120

SQL> SQL> SELECT table_name, partition_name, compression, compress_for FROM user_tab_partitions where table_name='MYTEST';

TABLE_NAME PARTITION_NAME COMPRESS COMPRESS_FOR

------------------------------ ------------------------------ -------- ------------

MYTEST P1 ENABLED QUERY HIGH

MYTEST P2 ENABLED OLTP

Tag : Oracle Exadata/ Advanced Compression / EHCC