Online Partition operations

• Table Partitions and subpartitions can now be moved online.

SQL> ALTER TABLE TEST MOVE PARTITION TEST_2012 ONLINE;

• Compression options can also be added during an online partition move.

SQL> ALTER TABLE TEST MOVE PARTITION TEST_2012 COMPRESS FOR QUERY UPDATE INDEXES ONLINE;

Reference Partitioning Enhancements

Truncate or Exchange Partition with Cascade option

• With Oracle 12c, it is now possible to use the CASCADE option to cascade operations to a child-referenced table when you TRUNCATE PARTITION or EXCHANGE PARTITION.
  • A parent table ORDERS
  • A child-referenced table INVOICE containing invoces for the corresponding orders in the first table.

Interval Reference Partitioning

• Before Oracle 12c you can’t use an interval-partitioned table as a parent table for reference partitioning, it is now possible.

CREATE TABLE par(pk INT CONSTRAINT par_pk PRIMARY KEY, i INT)
 PARTITION BY RANGE(i) INTERVAL (10)
 (PARTITION p1 VALUES LESS THAN (10));

CREATE TABLE chi(fk INT NOT NULL, i INT,
 CONSTRAINT chi_fk FOREIGN KEY(fk) REFERENCES par(pk))
 PARTITION BY REFERENCE(chi_fk);

 Truncate with Cascade option

• If you use the CASCADE option to truncate a parent table, all the child tables will be truncated too.

Multipartition Maintenance Operations

 Adding multiple partitions

• You can now add multiple partitions at the same time with the ADD PARTTITION or ADD SUBPARTITION clauses:

ALTER TABLE sales ADD 
  PARTITION sales_q1_2007 VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy')),
  PARTITION sales_q2_2007 VALUES LESS THAN (TO_DATE('01-JUL-2007','dd-MON-yyyy')),
  PARTITION sales_q3_2007 VALUES LESS THAN (TO_DATE('01-OCT-2007','dd-MON-yyyy')),
  PARTITION sales_q4_2007 VALUES LESS THAN (TO_DATE('01-JAN-2008','dd-MON-yyyy'))
;

 Truncate multiple partitions

• You can now truncate multiple partitions at a time:

ALTER TABLE sales TRUNCATE PARTITIONS sales_q1_2008, sales_q2_2008, 
     sales_q3_2008, sales_q4_2008;

•  Be careful if you don’t specify the UPDATE INDEXES clause in you ALTER command, you have to rebuild the indexes after.

Merging Multiple Partitions

• You can now MERGE multiple partitions or subpartitions at a time:

ALTER TABLE t1 MERGE PARTITIONS p01, p02, p03, p04 INTO p0;

 Splitting Into Multiple Partitions

• You can split a partition or a subpartition into multiples partitions or subartition using the SPLIT PARTITION or SPLIT SUBPARTITION clause:

ALTER TABLE SPLIT PARTITION p0 INTO 
  (PARTITION p01 VALUES LESS THAN (25),
   PARTITION p02 VALUES LESS THAN (50), 
   PARTITION p03 VALUES LESS THAN (75),
   PARTITION p04);

ALTER TABLE SPLIT PARTITION p0 INTO 
  (PARTITION p01 VALUES LESS THAN (25),
   PARTITION p02);

 Dropping Multiple Partitions

• You can now drop multiple partitions or subpartitions at a time using the DROP PARTITION or DROP SUBPARTITION clause:

ALTER TABLE sales DROP PARTITION sales_q1_2008, sales_q2_2008,
     sales_q3_2008, sales_q4_2008;

Index Enhancements for Partitioned Tables

Partial Index for Partitioned Tables

•  Oracle 12c introduces the flexibility to create partial indexes on subset of a partition or subpartition.
• These indexes can be either global or local indexes.
• You can use the INDEXING FULL or INDEXING PARTIAL depending you want a partial or full index, the default is INDEXING FULL.
• Partial Indexes can also be updated asynchronously speeding up the DROP/TRNCATE operations.

CREATE TABLE orders (
  order_id NUMBER(12),
  order_date DATE CONSTRAINT order_date_nn NOT NULL,
  order_mode VARCHAR2(8),
  customer_id NUMBER(6) CONSTRAINT order_customer_id_nn NOT NULL,
  order_status NUMBER(2),
  order_total NUMBER(8,2),
  sales_rep_id NUMBER(6),
  promotion_id NUMBER(6),
  CONSTRAINT order_mode_lov CHECK (order_mode in ('direct','online')),
  CONSTRAINT order_total_min CHECK (order_total >= 0))
   INDEXING OFF
   PARTITION BY RANGE (ORDER_DATE)
   (PARTITION ord_p1 VALUES LESS THAN (TO_DATE('01-MAR-1999','DD-MON-YYYY')) 
     INDEXING ON,
   PARTITION ord_p2 VALUES LESS THAN (TO_DATE('01-JUL-1999','DD-MON-YYYY')) 
     INDEXING OFF,
   PARTITION ord_p3 VALUES LESS THAN (TO_DATE('01-OCT-1999','DD-MON-YYYY')) 
     INDEXING ON,
   PARTITION ord_p4 VALUES LESS THAN (TO_DATE('01-MAR-2000','DD-MON-YYYY')),
   PARTITION ord_p5 VALUES LESS THAN (TO_DATE('01-MAR-2010','DD-MON-YYYY')));

•  When the indexing is turned off like for ORD_P2, the global partial indexes are not updated with corresponding values.
• The local partial indexes are also created in UNUSABLE mode when indexing is turned off.
• If the indexing clause is not specified, indexes are create UNUSABLE by default.