how to disable HCC

See also this link
Exadata: How To Disable Exadata Hybrid Columnar Compression? [ID 1080301.1]

olution

1) Create the partitioned tables and define each partition with the desired compression option:

a) Archive Low
b) Archive high
c) Query Low
d) Query High


[oracle@dmorldb01 ~]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Thu Apr 1 16:14:14 2010

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> create table orders (cid numeric(10))
partition by range (cid)
(partition p1 values less than (100000) nocompress,
partition p2 values less than (200000) compress for archive low,
partition p3 values less than (300000) compress for query high,
partition p4 values less than (maxvalue) compress for query low)
enable row movement;
2 3 4 5 6 7
Table created.

2) Display the table structure to verify the compression option associated with each partition:


SQL> select PARTITION_NAME,COMPRESSION, COMPRESS_FOR
from DBA_tab_partitions
where TABLE_NAME like 'ORDERS';
2 3
PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
P1 DISABLED
P2 ENABLED ARCHIVE LOW
P3 ENABLED QUERY HIGH
P4 ENABLED QUERY LOW

3) Now disable the HCC compression on the desired partition:


SQL> alter table ORDERS modify partition P2 nocompress;

Table altered.

4) Verify the HCC compression was disabled:

SQL> select PARTITION_NAME,COMPRESSION, COMPRESS_FOR
from DBA_tab_partitions
where TABLE_NAME like 'ORDERS'; 2 3

PARTITION_NAME COMPRESS COMPRESS_FOR
------------------------------ -------- ------------
P1 DISABLED
P2 DISABLED <(== 
P3 ENABLED QUERY HIGH
P4 ENABLED QUERY LOW