Oracle 12c Partitioning New Features - Top 10

Ever since I have written Top features of Oracle 12c R2 database, many of my friends have asked me which one is my favourite one. I loved a-lot of features of Oracle 12c database but as a developer my favorite are In-Memory feature, JSON Support and Enhancement on Table Partitions. I think Oracle has done a great job by making maintenance of partitions very easy and almost Online. So With this post I am trying to cover Top 10 New Partitioning Features with Oracle 12c database (both of R1 and R2)

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
  );

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.


Related Posts:
- Oracle 18c Database - Autonomous Database Cloud
- Oracle Database 12c R2 New Features for developers
- Oracle Database 12c R1 New Features for Developers
- JSON in Oracle Database 12c with Examples
- Oracle Database 11g New Features for Developers
- SQL Interview Question Answers