COMPRESSION -- 2 : Compressed Table Partitions

A Partitioned Table can choose to have a mix of COMPRESS and NOCOMPRESS Partitions.

As in this case where the first 3 partitions are defined as COMPRESS and the last as NOCOMPRESS :  :

[oracle@localhost Hemant]$ sqlplus hemant/hemant@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Sun Mar 6 23:20:19 2016

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

Last Successful login time: Sun Mar 06 2016 23:19:11 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> @create_SALESHIST
SQL> spool create_SALESHIST
SQL> 
SQL> drop table SALESHIST;

Table dropped.

SQL> 
SQL> alter session set "_partition_large_extents"=FALSE;

Session altered.

SQL> 
SQL> create table SALESHIST
  2   (
  3    PROD_ID NUMBER NOT NULL ,
  4    CUST_ID NUMBER NOT NULL ,
  5    TIME_ID DATE NOT NULL ,
  6    CHANNEL_ID NUMBER NOT NULL ,
  7    PROMO_ID NUMBER NOT NULL ,
  8    QUANTITY_SOLD NUMBER(10,2) NOT NULL ,
  9    AMOUNT_SOLD NUMBER(10,2) NOT NULL
 10   )
 11   NOCOMPRESS LOGGING
 12    TABLESPACE USERS
 13    PARTITION BY RANGE (TIME_ID)
 14   (
 15    PARTITION SALES_1998
 16    VALUES LESS THAN (TO_DATE('1999-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 17        'NLS_CALENDAR=GREGORIAN'))
 18    SEGMENT CREATION IMMEDIATE
 19    COMPRESS BASIC NOLOGGING
 20    TABLESPACE SALES_1998 ,
 21    PARTITION SALES_1999
 22    VALUES LESS THAN (TO_DATE('2000-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 23        'NLS_CALENDAR=GREGORIAN'))
 24    SEGMENT CREATION IMMEDIATE
 25    COMPRESS BASIC NOLOGGING
 26    TABLESPACE SALES_1999 ,
 27    PARTITION SALES_2000
 28    VALUES LESS THAN (TO_DATE('2001-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 29        'NLS_CALENDAR=GREGORIAN'))
 30    SEGMENT CREATION IMMEDIATE
 31    COMPRESS BASIC NOLOGGING
 32    TABLESPACE SALES_2000 ,
 33    PARTITION SALES_2001
 34    VALUES LESS THAN (TO_DATE('2002-01-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS',
 35        'NLS_CALENDAR=GREGORIAN'))
 36    SEGMENT CREATION IMMEDIATE
 37    NOCOMPRESS NOLOGGING
 38    TABLESPACE SALES_2001 )
 39  /

Table created.

SQL> 
SQL> spool off
SQL> 
SQL> col partition_name format a30
SQL> select partition_name, compression, compress_for
  2  from user_tab_partitions
  3  where table_name = 'SALESHIST'
  4  order by partition_position;

PARTITION_NAME         COMPRESS COMPRESS_FOR
------------------------------ -------- ------------------------------
SALES_1998         ENABLED BASIC
SALES_1999         ENABLED BASIC
SALES_2000         ENABLED BASIC
SALES_2001         DISABLED

SQL> 
SQL> select compression, compress_for
  2  from user_tables
  3  where table_name = 'SALESHIST';

COMPRESS COMPRESS_FOR
-------- ------------------------------


SQL> 

The Compression attributes actually apply to the Segments so the Partition Segments have the definition but the Table, being segmentless, does not show the definition.

Note that I am still demonstrating BASIC Compression. So the compression is applied only on Direct Path INSERT.  As in :

SQL> insert /*+ APPEND */ into saleshist select * from sh.sales;

918843 rows created.

SQL> commit;

Commit complete.

SQL> 
SQL> select partition_name, tablespace_name, bytes/1024
  2  from user_segments
  3  where segment_name = 'SALESHIST'
  4  and segment_type = 'TABLE PARTITION'
  5  order by 1;

PARTITION_NAME         TABLESPACE_NAME        BYTES/1024
------------------------------ ------------------------------ ----------
SALES_1998         SALES_1998       3072
SALES_1999         SALES_1999       4096
SALES_2000         SALES_2000       4096
SALES_2001         SALES_2001      11264

SQL> 

This demonstrates that it is possible to
a) Have some Partitions defined as COMPRESSed and others as NOCOMPRESSed
b) Create different Partitions in different Tablespaces

Before the advent of (and still without using) the 12c ILM features, this was and is a method to manage historical data with compression and moving or placing data (i.e. the respective tablespace datafile(s)) in low-cost storage as desired.

Note : For the purposes of this demo, I used the parameter "_partition_large_extents"=FALSE.  Oracle's default Partition size since 11.2.0.2 has been 8MB for Table Partitions and that would have been excessively large for this demo.  If you are using Hybrid Columnar Compression and/or Exadata, Oracle advises against this.
.
.
.