Oracle 12c R2: Partitioning Enhancements

In this article, I would like to talk about the improvements in partitioning that came with Oracle 12c R2.

Partitioning is one of Oracle's most basic and favorite features. It's a database feature that increases our overall query performance, especially for large tables.

Before Oracle 12c R2, we could create a table physically partitioned only during the creation phase. When we wanted to be partitioned a rapidly growing non-partitioned table, we could not perform this operation directly, and we were experiencing decreasing in our performance of accessing the data.

Look at the situation in Oracle 11g R2:

//db_version: 11g R2
CREATE TABLE partitionTest
(
   YEARMONTH        NUMBER,
   INVOICE_ID       NUMBER,
   PRICE            NUMBER
);

INSERT /*+append*/
      INTO  partitionTest
       SELECT TO_NUMBER (TO_CHAR (SYSDATE - MOD (LEVEL, 365), 'YYYYMM'))
                 YEARMONTH,
              LEVEL INVOCE_ID,
              MOD (LEVEL, 365) * 10 price
         FROM DUAL
   CONNECT BY LEVEL < 100000;

COMMIT;

ALTER TABLE partitionTest MODIFY
PARTITION BY RANGE (YEARMONTH)
  (PARTITION P201606 VALUES LESS THAN (201607),
      PARTITION P201607 VALUES LESS THAN (201608),
      PARTITION P201608 VALUES LESS THAN (201609),
      PARTITION P201609 VALUES LESS THAN (201610),
      PARTITION P201610 VALUES LESS THAN (201611),
      PARTITION P201611 VALUES LESS THAN (201612),
      PARTITION P201612 VALUES LESS THAN (201701),
      PARTITION P201701 VALUES LESS THAN (201702),
      PARTITION P201702 VALUES LESS THAN (201703),
      PARTITION P201703 VALUES LESS THAN (201704),
      PARTITION P201704 VALUES LESS THAN (201705),
      PARTITION P201705 VALUES LESS THAN (201706),
      PARTITION P201706 VALUES LESS THAN (201707))

ORA-14006: invalid partition name

Script Terminated on line 21.

If I want to create a partitioned structure in Oracle 11g R2 as a non-partitioned table created above, I can not do this directly on the table.

Now, let's try this operation with Oracle 12c R2:

//db_version: 12C R2
CREATE TABLE partitionTest
(
   YEARMONTH        NUMBER,
   INVOICE_ID       NUMBER,
   PRICE            NUMBER
);

INSERT /*+append*/
      INTO  partitionTest
       SELECT TO_NUMBER (TO_CHAR (SYSDATE - MOD (LEVEL, 365), 'YYYYMM'))
                 YEARMONTH,
              LEVEL INVOCE_ID,
              MOD (LEVEL, 365) * 10 price
         FROM DUAL
   CONNECT BY LEVEL < 100000;

   COMMIT;

ALTER TABLE partitionTest MODIFY
PARTITION BY RANGE (YEARMONTH)
  (PARTITION P201606 VALUES LESS THAN (201607),
      PARTITION P201607 VALUES LESS THAN (201608),
      PARTITION P201608 VALUES LESS THAN (201609),
      PARTITION P201609 VALUES LESS THAN (201610),
      PARTITION P201610 VALUES LESS THAN (201611),
      PARTITION P201611 VALUES LESS THAN (201612),
      PARTITION P201612 VALUES LESS THAN (201701),
      PARTITION P201701 VALUES LESS THAN (201702),
      PARTITION P201702 VALUES LESS THAN (201703),
      PARTITION P201703 VALUES LESS THAN (201704),
      PARTITION P201704 VALUES LESS THAN (201705),
      PARTITION P201705 VALUES LESS THAN (201706),
      PARTITION P201706 VALUES LESS THAN (201707)) ONLINE;

Table PARTITIONTEST created.

99.999 rows inserted.

Commit complete.

Table PARTITIONTEST altered.

Now, let's check the partitions:

SELECT table_name, partition_name, high_value
  FROM all_tab_partitions
 WHERE table_name = 'PARTITIONTEST';

Yes, as you can see, I could partition a non-partitioned table after writing the data into the table. The ONLINE command that I used at the end of the ALTER TABLE ... MODIFY clause to do this allowed the DML operations to be processed concurrently in the table while physically partitioning the table. So, I did not have any downtime while the table was doing a great physical change.

This innovation, coupled with Oracle 12c R2, has significantly improved the non-partitioned tables by removing the operational and downtime costs of partitioning.