Oracle 12c Partitioning New Features

1. Automatic List Partition
With Automatic List Partitions, Oracle 12.2 Database will create new partition for every distinct value of list partitioning key. 
Example:

CREATE TABLE CUSTOMERS

(

   CUST_ID          NUMBER NOT NULL,

   CUST_NAME        VARCHAR2 (200 BYTE) NOT NULL,

   STREET_ADDRESS   VARCHAR2 (40 BYTE) NOT NULL,

   CITY_ID          NUMBER NOT NULL,

   COUNTRY_CD       VARCHAR2 (5) NOT NULL,

   CUST_PHONE       VARCHAR2 (50 BYTE),

   JOINING_DATE     DATE

)

PARTITION BY LIST (COUNTRY_CD) AUTOMATIC

(

  PARTITION CUSTOMERS_IND VALUES ('IND'),

  PARTITION CUSTOMERS_USA VALUES ('USA')

);

You can even alter a old List Partitioned table to Automatic List Partitioned by simply

ALTER TABLE CUSTOMERS SET PARTITIONING AUTOMATIC;

2. Partition a Non-partitioned Table Online
From Oracle 12c R2, We can easily convert a Nonpartitioned Table to a Partitioned Table Online (along with Indexes) without using DBMS_REDEFINITION. Lets suppose CUSTOMERS table is non-partitioned

ALTER TABLE CUSTOMERS MODIFY

  PARTITION BY RANGE (JOINING_DATE) (

    PARTITION CUSTOMERS_P2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')),

    PARTITION CUSTOMERS_P2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),

    PARTITION CUSTOMERS_P2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))

  ) ONLINE

  UPDATE INDEXES

  (

    CUSTOMERS_PK GLOBAL,

    CUST_JOININGDT_INDX LOCAL

  );

What happened to the indexes? The rules are:

·       Global partitioned indexes are untouched and retain their shape.

·       Non-prefixed indexes will become global non partitioned tables.

·       Prefixed indexes will become local partitioned indexes.

·       Bitmap indexes will become local partitioned indexes.

3. Table Creation for Partition Exchange
Before Oracle 12c R2, all of us must have faced "ORA-14097: column type or size mismatch in ALTER TABLE EXCHANGE PARTITION" a-lot of time. With "FOR EXCHANGE WITH TABLE" clause, Oracle 12c R2 has made our life easier by creating the new table empty with correct structure 

CREATE TABLE CUSTOMERS_TEMP

FOR EXCHANGE WITH TABLE CUSTOMERS;

and now we can use CUSTOMERS_TEMP in ALTER TABLE EXCHANGE PARTITION without any issue

ALTER TABLE CUSTOMERS

  EXCHANGE PARTITION CUSTOMERS_P2017

  WITH TABLE CUSTOMERS_TEMP

  WITHOUT VALIDATION

  UPDATE GLOBAL INDEXES;

4. Multiple Partition Management
From Oracle 12c R2, we can do most of the maintenance task on Multiple Partitions in a single command, like
a) Add multiple partitions

alter table CUSTOMERS add

    PARTITION CUSTOMERS_P2018 VALUES LESS THAN (TO_DATE('01-JAN-2019','DD-MON-YYYY')),

    PARTITION CUSTOMERS_P2019 VALUES LESS THAN (TO_DATE('01-JAN-2020','DD-MON-YYYY')),

    PARTITION CUSTOMERS_P2020 VALUES LESS THAN (TO_DATE('01-JAN-2021','DD-MON-YYYY'));

b) Drop multiple partitions

alter table CUSTOMERS drop partitions CUSTOMERS_P2018, CUSTOMERS_P2019, CUSTOMERS_P2020;

c) Truncate multiple partitions

alter table CUSTOMERS truncate partitions CUSTOMERS_P2018, CUSTOMERS_P2019, CUSTOMERS_P2020;

d) Merge multiple partitions

alter table CUSTOMERS merge partitions CUSTOMERS_P2018, CUSTOMERS_P2019, CUSTOMERS_P2020;

e) Split multiple partitions

alter table CUSTOMERS split partitions CUSTOMERS_P2018

partition CUSTOMERS_P2018Q1 values less than (TO_DATE('01-APR-2018','DD-MON-YYYY')),

partition CUSTOMERS_P2018Q2 values less than (TO_DATE('01-JUL-2018','DD-MON-YYYY')),

partition CUSTOMERS_P2018Q3 values less than (TO_DATE('01-OCT-2018','DD-MON-YYYY')),

partition CUSTOMERS_P2018Q4;

5. Partial Indexes
With 12c, we can create an INDEX on subset of partitions. We need to set INDEXING OFF for the individual partition while creating the table

CREATE TABLE CUSTOMERS

(

   CUST_ID          NUMBER NOT NULL,

   CUST_NAME        VARCHAR2 (200 BYTE) NOT NULL,

   STREET_ADDRESS   VARCHAR2 (40 BYTE) NOT NULL,

   CITY_ID          NUMBER NOT NULL,

   COUNTRY_CD       VARCHAR2 (5) NOT NULL,

   CUST_PHONE       VARCHAR2 (50 BYTE),

   JOINING_DATE     DATE

)

PARTITION BY RANGE (JOINING_DATE) (

    PARTITION CUSTOMERS_P2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','DD-MON-YYYY')) INDEXING OFF,

    PARTITION CUSTOMERS_P2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','DD-MON-YYYY')),

    PARTITION CUSTOMERS_P2017 VALUES LESS THAN (TO_DATE('01-JAN-2018','DD-MON-YYYY'))

);

or we can alter them later

ALTER TABLE CUSTOMERS MODIFY PARTITION CUSTOMERS_P2015 INDEXING OFF;

Now to create Partial Indexes, we need to add INDEXING PARTIAL clause while creating index.

CREATE INDEX CUST_NAME_IDX ON CUSTOMERS(CUST_NAME) LOCAL INDEXING PARTIAL;

6. TRUNCATE partition Cascade 
With 12c R1, we can use Cascade keyword with TRUNCATE PARTITION command to TRUNCATE the referenced child partitions. The FOREIGN keys must be defined with ON DELETE CASCADE for TRUNCATE PARTITION .. CASCADE to work. 


7. Moving Partitions Online
With 12c, we can move table partitions and sub-partitions ONLINE i.e. without blocking DML operations. Parallel DML and direct path loads are not supported on the partitions being moved online.

ALTER TABLE CUSTOMERS MOVE PARTITION CUSTOMERS_P2015 ONLINE TABLESPACE USERS_NEW UPDATE INDEXES;

8. Asynchronous Global Index Maintenance 
Global Indexes used to get UNUSABLE with various partition maintenance activities with pre Oracle 12c databases, and need to be maintained manually. With Oracle 12c if we DROP or TRUNCATE a Partition global index is not marked unusable and Index maintenance is done asynchronously behind the scene.


9. Incremental Statistics for Partitioned Tables
By Incremental statistics Oracle can gather statistics at partition level and calculate the global-level statistics accurately, reducing the time and system resources utilization. 
Before Oracle 12c, Partition-level statistics were considered stale if any DML has been executed and were not used in Incremental Statistics. Now, we can set an incremental staleness threshold so that even after some DML operation partition statistics can be used by incremental statistics.


10. Read-Only Partitions
Before Oracle 12c we can set a table as Read Only to disables DML operations on the table. Now we can do the same on the individual Partitions and sub-partitions.

create table ropt (col1, col2, col3) nocompress

partition by range (col1) interval (10)

(partition p1 values less than (1) read only,

 partition p2 values less than (11))

as select rownum, rownum*10, rpad('a',rownum,'b')

from dual connect by level <= 100;

select partition_name, high_value, read_only

from user_tab_partitions

where table_name='ROPT';

alter table ropt split partition for (5) into

(partition pa values less than (7), partition pb read only) online;

 11.Let's move and compress this Partitions

alter table ropt move partition pb compress for oltp;